Access
Parsing out City State ZIP
Here is some sample code to parse out the City State and ZIP form a string.
Example usage:
? GetCity("Some Town, OK 73101")
Some Town
? GetCity("Some Town OK 73101")
Some Town
? GetState("Some Town, OK 73101")
OK
? GetState("Some Town OK 73101")
OK
? GetZIP("Some Town, OK 73101-0000")
73101-0000
? GetZIP("Some Town, OK 73101")
73101
Following is the VBA code for the functions used above:
Code:
' ******** Code Start ********
'This code was originally written by Boyd Trimmell aka HiTechCoach.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of 'Boyd Trimmell
'
Public Function FlipIt(InData As String) As String
Dim p As Integer
Dim strOut As String
strOut = ""
For p = Len(InData) To 1 Step -1
strOut = strOut & Mid(InData, p, 1)
Next p
FlipIt = strOut
End Function
Public Function GetCity(InData As String) As String
Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long
strWRK = FlipIt(Trim(InData))
strOut = ""
p = InStr(strWRK, " ")
If p > 0 Then
' found a zip end
strWRK = Trim(Mid(strWRK, p))
p2 = InStr(strWRK, " ")
If p2 > 0 Then
' found state end
strWRK = Trim(Mid(strWRK, p2))
' found city
strOut = strWRK
' remove a , from end of city
If Left(strOut, 1) = "," Then
strOut = Trim(Mid(strOut, 2))
End If
strOut = FlipIt(strOut)
End If
End If
GetCity = strOut
End Function
Public Function GetState(InData As String) As String
Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long
strWRK = FlipIt(Trim(InData))
strOut = ""
p = InStr(strWRK, " ")
If p > 0 Then
' found a zip end
strWRK = Trim(Mid(strWRK, p))
p2 = InStr(strWRK, " ")
' found state end
strOut = FlipIt(Trim(Left(strWRK, p2)))
End If
GetState = strOut
End Function
Public Function GetZIP(InData As String) As String
Dim strOut As String
Dim strWRK As String
Dim p As Long
Dim p2 As Long
Dim p3 As Long
strWRK = FlipIt(Trim(InData))
strOut = ""
p = InStr(strWRK, " ")
If p > 0 Then
' found a zip end
p2 = InStr(p, strWRK, " ")
strOut = FlipIt(Trim(Left(strWRK, p)))
End If
GetZIP = strOut
End Function
' ********** Code End **********