Parsing String Into Array

[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

 

[HarleyQuinn]

The myArr variable you have will hold an array of the split data. Try this as an example to see what it does.

Dim myArr as Variant
Dim strToSplit as String
Dim i as Integer

strToSplit = "1:2:3"

myArr = Split(strToSplit,":",-1)

For i = 0 To Ubound(myArr) '-1
msgbox myarr(i)
Next i

That should help you understand how the function works, and the help is also quite good on Split() so you can get the correct syntax to use as the one you are currently using won't work as the Split() function is not called correctly.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

Also a search on this site for Split() should turn up several examples.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.