Parse City State and Zip code

[Natalie]

I need some help in seperating the city state and zip code data into 3
individual fields. the data is collected into one column, but our user group
would like to seperate it for reporting purposes and mailing list purposes.


I used the following function to extract the first word before the space in
the column, however it doesn't work where the city is represented by 2 words.

ANYWHERE FL 33360

ANY WHERE FL 33360

what's the best way to go about getting the data how I want it presented?

Thanks for your help.

 

[Jeff Boyce]

Natalie

This is a bit like parsing a "FullName" string ... your [City] could consist
of one (or more) words, separated by ??spaces (??hyphens).

And your zip (postal) code, will it always be 5 characters long, or could it
include the "plus 4" designation?

One approach might be considered a bit of a kludge, and would depend on
which version of Access you are using:

If you use the Reverse() function, you get a string of characters (in
reverse) that starts with the postal code (in reverse), then one/more
spaces, then the State/Province (in reverse), then everything left would be
the city (in reverse). You could strip off the left-most characters (up to
the first space) using Left() and Instr() functions, then save that after
re-Reverse-ing it as the postal code.

If you then start with the remainder, after stripping out the postal code
(in reverse), use the LTrim() function to eliminate any leading spaces, you
could again use the next space as the divider between state/province and
city. Remember to re-Reverse.

Finally, anything leftover after stripping out the state/province portion
will be the city (after re-Reverse-ing, right?).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP