Parsing Inconsistent Addresses

[derekstewart]

I have an address dataset in access that I need to parse into seperate City, State and Zip fields. Here is a sample from the data. Sometimes there is a comma, other times just a space. There are various numbers of blank spaces after the address field. Any ideas? Thanks. Derek

CityStateZip
AUSTIN, TX 78702
PITTSBURGH, PA 15264
ARLINGTON TX 76096
INDIANAPOLIS, IN 46205
LAMPASAS TX 76550
WESTMINSTER, CO 80234
CAMERON,TEXAS 76520

 

[PHV]

I'm afraid you have to code your own function.
Tip (if ac2k or above):
Dim myArr
myArr = Split(Replace(vCityStateZip, ",", " ")
For i = 0 To Ubound(myArr)
If Trim(myArr(i)) <> "" Then
...
End If
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[derekstewart]

I don't understand the code but will take what you sent and try and make sense of it. Thanks. Derek

 

[lespaul]

SPLIT is a function in ac2k and above that takes a field like:
AUSTIN TX 78702

and splits it up based on a where it finds spaces. In the example PHV provided, first it replaces the comma with a " " and then it creates an array that splits the text up where it finds a space.

So you would end up with:

myArr(0) = AUSTIN
myArr(1) = TX
myArr(2) = 78702

now you can use the values in the array to fill in the correct fields in the table.

HTH

leslie

 

[derekstewart]

I have an address dataset in access that I need to parse into seperate City, State and Zip fields. Here is a sample from the data. Sometimes there is a comma, other times just a space. There are various numbers of blank spaces after the address field.

CityStateZip
AUSTIN, TX 78702
PITTSBURGH, PA 15264
ARLINGTON TX 76096
INDIANAPOLIS, IN 46205
LAMPASAS TX 76550
WESTMINSTER, CO 80234
CAMERON,TEXAS 76520

I've been given the following code, but honestly don't know what to do with it:

Dim myArr
myArr = Split(Replace(vCityStateZip, ",", " ")
For i = 0 To Ubound(myArr)
If Trim(myArr(i)) <> "" Then
...
End If
Next i

I'm not sure what it's doing or how to get it to display the info in the query. I see that the split and replace code removes the commas, replacing them with spaces. Then I'm not sure what it does from there. How does it parse? If it holds my data, how do I show the data in the array? If I create the code and put it in the query (i.e. myARR(citystatezip)does it automatically create 3 columns with the city, state, and zip. I'm really unfamiliar with code. Any help would be appreciated. Thanks. D