Search String to Update New Field - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search String to Update New Field

Dg Ewing

9 months ago

I have imported records with address that look like this: I will upload a picture.

There was no discipline wrt to data input, I want to update a new field "State" based on the two digit capitalized state abrv. i.e. VA, WV, NJ etc.

I recognize that I will only get a 80-90% solution and I know how to do this if I wrote 50 update queries , one for each state. I am hoping that there is a better VBA scheme that I only have to duplicate maybe 50 lines of code.

	Dg Ewing OP @Reply 9 months ago

	Dg Ewing OP @Reply 9 months ago
and I should have said or the spelled out name In addition to the two digit abv. ie VA or Virginia

	Adam Schwanz @Reply 9 months ago
So to clarify, you want to get the list of any words that are 2 capital letters side by side, and you know there will be some that don't belong possibly. I'm not sure what the whole table looks like but you might have two capitals for other words in there because there was no data consistency. So this is just a do the best to get as much of the info as you can. Is that right?

Adam Schwanz @Reply

9 months ago

Try something like this. Make a module and insert this code.

Function ExtractTwoCaps(str As String) As String

    Dim regEx As Object

    Dim matches As Object



    If IsNull(str) Or Len(Trim(str)) = 0 Then

        ExtractTwoCaps = ""

        Exit Function

    End If



    Set regEx = CreateObject("VBScript.RegExp")

    regEx.Pattern = "([A-Z]{2})"

    regEx.IgnoreCase = False

    regEx.Global = False  ' Only return the first match



    If regEx.Test(str) Then

        Set matches = regEx.Execute(str)

        ExtractTwoCaps = matches(0).Value

    Else

        ExtractTwoCaps = ""

    End If

End Function

Then in your query make a field like this (replace notes with your field)
ExtractedLetters: ExtractTwoCaps(Nz([Notes],""))

	Raymond Spornhauer @Reply 9 months ago
Dg https://www.youtube.com/watch?v=XyMOo0ehqkE

	Dg Ewing OP @Reply 9 months ago
Thanks Adam and Raymond, This is the criteria, that works for Tennessee and I have a table that has all 50 state abbreviations, but I would be ok duplicating this line 50 times if I have to.... Like "tn" Or Like "tn " or like ", tn " or like ", tn" Or Like ". tn" Or Like ". tn*"

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View