Can't separate my city from state and zip..

[option]

Hey guys,

I've got a module that breaks up a line of an address to separate city, state, and zip. It works great on single word cities like Denver or Atlanta, but with two or more words (Green Bay, West Palm Beach, etc) it bombs out. Can anyone take a peek and see how this could be adjusted? I'm at a loss. The code is basically looking for the first space in the string and cutting off the left-most section for the result, which is what I want, since the address is formatted as such. Here's what I've got:

Sub GetAddSep()
Dim intPos As Integer
Dim FullAdd As String
Dim test As String
Dim Bcity As String, Bstate As String, Bzip As String
Dim strLeftPart As String
On Error GoTo Err_Command0_Click

'intPos = InStr(strName, " ")

        
        '---Format Address---'
        FullAdd = "SACRAMENTO CA 99999"    'a string like this works.  Change Sacramento to SAN DIEGO or WEST PALM BEACH..epic fail.
        
        test = Right(FullAdd, 10)
        
        If Mid(test, 6, 1) = "-" Then
         Bzip = Mid(test, 1, 5)
        Else
         Bzip = Right(FullAdd, 5)
        End If
        
         Bcity = Mid([FullAdd], 1, InStr([FullAdd], " ") - 1) 'works for single cities
         strLeftPart = Mid([FullAdd], InStr([FullAdd], " ") + 2, Len([FullAdd]))
         Bstate = Mid(Trim([test]), 1, InStr([test], " ") + 2)
        
           
Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
End Sub

Thanks!!

 

[MSAccessRookie]

If I understand you correctly, your string consists of the following:

• A City Name (Variable in Length and containing 0 or more spaces)
• A Space Character
• A State Code (Exactly two characters)
• A Space Character
• A Zip Code (5 or 10 Characters depending on the existance of a "-" Character)

Try a reverse approach.

• Find the Zip Code (Like you are now should be OK) and remember its Start Position
• The State Code will start three Characters before that
• The City name is the rest of the line.

See if something like that will work

 

[option]

Great idea! I did just that, and it worked. I'll share the code as well. It wasn't a huge change either (thankfully!). Thanks a million!!

Sub GetAddSep()
Dim intPos As Integer
Dim FullAdd As String
Dim test As String
Dim Bcity As String, Bstate As String, Bzip As String
Dim strLeftPart As String
On Error GoTo Err_Command0_Click

'intPos = InStr(strName, " ")

        
         '---Format Address---'
        FullAdd = "TAMPA BAY FL 99999"
        
        test = Right(FullAdd, 10)
        
        
        If Mid(test, 6, 1) = "-" Then
         Bzip = Mid(test, 1, 5)
        Else
         Bzip = Right(FullAdd, 5)
        End If
        
         strLeftPart = Mid([FullAdd], InStr([FullAdd], " ") + 2, Len([FullAdd]))
         Bstate = Mid(Trim([test]), 1, InStr([test], " ") + 2)
         intPos = InStr(FullAdd, Bstate)
         Bcity = Mid([FullAdd], 1, InStr([FullAdd], Bstate) - 3)
        
           
Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
End Sub

 

[Steve R.]

@MSAccessRookie - Interesting solution, one that I would not have thought of.

I had a "similar" situation where I separated the first and last names. I solved this by keeping track of the number of spaces. For example, the State Code is a known "landmark", so if you have more than one space character by the time you get to the State code you know that the City name consists of two or more strings.

Another potential "landmark" or issue is the period "." as in St. Augustine. If a period is found in the line you know you have a compound name.