Splitting Addresses

[steve229922]

I'm trying to split an address field into 3 parts- City, State, Zip. The data looks like this----

New York City NY 10025
S. Yarmouth MA 02664

I've been using update queries, but this format is causing a bit of a problem. The conventional Instr, Left, Mid, Len functions will not seem to work because of the extra spaces in the City Name.

Any sugesstions on how to isolate the City, Sate, and Zip seperately, would help tremendously!!! Or a creative way to use the functions above that I can't seem to figure out--

Thank You!

 

[CajunCenturion]

You might try using the SPLIT function to separate the address into individual words. Then the zip will be the last element in the array, state will be 2nd from top, and 0 to one less can be rejoined as the city.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[lewds]

Hi...

Here is a little test...

open a new form with 3 textboxes

if your information is always in the same format this should be what you need

Private Sub Form_Load()
Dim city, state, zip, Mystring As String
Mystring = "New York City NY 10025"
zip = Right(Mystring, 5)
Mystring = Left(Mystring, Len(Mystring) - 6)
state = Right(Mystring, 2)
city = Left(Mystring, Len(Mystring) - 3)
Text1.SetFocus
Text1.Text = city
Text3.SetFocus
Text3.Text = state
Text5.SetFocus
Text5.Text = zip
End Sub