break up city,state zip to multiple fields

[threeo2]

I have a field on a form that contains city,state,zip,zip+4. example:

New York, NY 01341-1111

This is all in one field on one line. I need to hit a button and strip the data from the one field, then copy to the appropriate fields on the same form. example:

[City] = New York
[State] = NY
[ZipCode] = 01341
[Zip+4] = 1111

obviously the length of the city is not fixed but the state abbreviation is always 2 characters, preceded by the comma, followed by the fixed length 5 digit Zip Code, a dash ("-") and the 4 digit Plus 4.

Seems like a fairly common problem to us folks in the U.S. Does anybody have some code to break this up?

Thnx.

 

[dcx693]

Have you searched the forum? I'm guessing this is a fairly common request.

Since all of your data is fairly uniform, you can use an update query employing string functions like Left, Mid, Right and Instr to separate out the data. Alas, I do not have anything pre-made.

 

[threeo2]

I have searched the forum but couldn't find an all-in-one fix. Since this should be a common problem i thought someone must have some code they keep handy for this. String functions like Left, Mid, Right and Instr will be find, I just don't know how to write them yet because i'm a newbie.

Thnx.

 

[Pat Hartman]

There are solutions here. Try using the keyword - parsing or parse.