City State Zip in one field

[Scott]

I have a table where the user entered the City State Zip
in the same field, and not in the same format, some have
01234-5678 and some have just 12345, the city and state
are sometimes separated with a comma like
City, State Zip
And some are just
City State Zip
Now, I want to have the city, State, and Zip in 3
separate fields would anyone be able to give me the SQL
for the update query. ?

Thanks a lot

Scott

 

[Dirk Goldgar]

I have a table where the user entered the City State Zip
in the same field, and not in the same format, some have
01234-5678 and some have just 12345, the city and state
are sometimes separated with a comma like
City, State Zip
And some are just
City State Zip
Now, I want to have the city, State, and Zip in 3
separate fields would anyone be able to give me the SQL
for the update query. ?

Thanks a lot

Scott

You're probably going to have to write a VBA function to parse out the
three parts of the field and return the piece requested (City, State, or
Zip). If all three parts are always present, you can probably get
around the need for a separate function, because you can first translate
all commas to blanks, then reduce multiple consecutive blanks in the
result to a single blank, and then take the last blank-delimited token
as the zip, the preceding token as the state (if it's always one word or
an abbreviation), and everything before that as the city name. This
sort of logic is necessary because cities often have more than one word
in their names.

Unless data entry is really regular, though, parsing of this sort is
usually best done in a separate function that your update query calls.

 

[Scott]

Could you provide me with such a function. ?


Scott

 

[Dirk Goldgar]

Could you provide me with such a function. ?


Scott

Here; I haven't tested it thoroughly, but try this:

'----- start of code -----
Function fncParseCityStateZip( _
pAddressLine As Variant, _
pWanted As String) _
As String

Dim strWork As String
Dim strCity As String
Dim strState As String
Dim strZip As String

Dim intPos As Integer

strWork = Trim$(pAddressLine & vbNullString)
If Len(strWork) = 0 Then Exit Function

intPos = InStr(1, strWork, ",", vbBinaryCompare)
If intPos > 0 Then
strCity = Trim$(Left$(strWork, intPos - 1))
strState = Trim$(Mid$(strWork, intPos + 1))
intPos = InStrRev(strState, " ", -1, vbBinaryCompare)
If intPos > 0 Then
strZip = Mid$(strState, intPos + 1)
strState = Trim$(Left$(strState, intPos - 1))
End If
Else
intPos = InStrRev(strWork, " ", -1, vbBinaryCompare)
If intPos = 0 Then
strCity = strWork
Else
strZip = Mid$(strWork, intPos + 1)
strState = Trim$(Left$(strWork, intPos - 1))
intPos = InStrRev(strState, " ", -1, vbBinaryCompare)
If intPos = 0 Then
strCity = strState
strState = vbNullString
Else
strCity = Trim$(Left$(strState, intPos - 1))
strState = Mid$(strState, intPos + 1)
End If
End If
End If

Select Case pWanted
Case "C": fncParseCityStateZip = strCity
Case "S": fncParseCityStateZip = strState
Case "Z": fncParseCityStateZip = strZip
End Select

End Function
'----- end of code -----

Use it like this:

UPDATE YourTable SET
City = fncParseCityStateZip(CityStateZipField, 'C'),
State = fncParseCityStateZip(CityStateZipField, 'S'),
Zip = fncParseCityStateZip(CityStateZipField, 'Z');

 

[Scott]

Thanks, it worked excellent, I'v updated more than 5000
records in 2 Seconds with some 10 minutes of touch-ups
where needed.

Thanks again

Scott