Extracting Zipcode out of address line

[Jerry Crosby]

I inherited a table in which the City, State and Zip are all in one field.
Some Zips are in the Zip+4 format. I need to sort the table by zipcode and
I've forgotten the coding for extracting the zip and putting it into a newly
created field (update query). I do not need to remove it from the original
field.

Any help? Thanks in advance.

Jerry

 

[John Vinson]

I inherited a table in which the City, State and Zip are all in one field.
Some Zips are in the Zip+4 format. I need to sort the table by zipcode and
I've forgotten the coding for extracting the zip and putting it into a newly
created field (update query). I do not need to remove it from the original
field.

Any help? Thanks in advance.

Jerry

This can get complicated. My street address is 31140 - but that's not
my zip code, although it looks like one!

Assuming that a) the zip is consistantly the last element in the
address, b) that it is preceded by a blank, and c) that you have
Access2000 or later, you should be able to create a calculated field:

Zip: Trim(Mid([Address], InStrRev([Address], " ")))


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[fredg]

I inherited a table in which the City, State and Zip are all in one field.
Some Zips are in the Zip+4 format. I need to sort the table by zipcode and
I've forgotten the coding for extracting the zip and putting it into a newly
created field (update query). I do not need to remove it from the original
field.

Any help? Thanks in advance.

Jerry

If all the data is in the following format (2 character state) ..
SomeCity, ST 12345-6789

You can use:
NewZip:Mid([FieldName],InStr([FieldName],",")+5)

 

[Jerry Crosby]

Yes, to a), b) and c), John. It worked fine! Thanks so much!

Jerry