Access 2003

[Jaci]

I am trying to create a mailing address database and would like to have the
option to enter the zip code and the city and state are automatically
populated. Any help is appreciated!

 

[Brian]

The easy part: create a ZIPS table that has three fields: ZIP, City, & State.
On your form, have a ZIP field in the After_Update of the ZIP code, have this
code:

City = DLookup("[City]","[ZIPS]","[ZIP] = " & [ZIP])
State = DLookup("[State]","[ZIPS]","[ZIP] = " & [ZIP])

This just tells Access to go find the city & state matching the ZIP. Of
course, it assumes that there will be only one city per ZIP, and although I
think that is accruatn, I'm not sure.

The hard part: getting all 42,000+ US ZIP codes into your ZIPS table (and
Canadian/other if desired). There are various places online where you can
purchase such a database, and I would suspect that one or more of these would
be in a format that you could import into your ZIPS table. There may even be
a free version from the post office, although I could not find it just now
with a little searching.

Or you could have an IT guru at a transportation company export the entire
database to an Access table for you from something like PCMiler or one of the
other mileage programs.

 

[Jaci]

Thank you I will give it a try. The good part for me is that I only need to
enter the zip codes in a predefined area -- not all 42,000.

The easy part: create a ZIPS table that has three fields: ZIP, City, & State.
On your form, have a ZIP field in the After_Update of the ZIP code, have this
code:

City = DLookup("[City]","[ZIPS]","[ZIP] = " & [ZIP])
State = DLookup("[State]","[ZIPS]","[ZIP] = " & [ZIP])

This just tells Access to go find the city & state matching the ZIP. Of
course, it assumes that there will be only one city per ZIP, and although I
think that is accruatn, I'm not sure.

The hard part: getting all 42,000+ US ZIP codes into your ZIPS table (and
Canadian/other if desired). There are various places online where you can
purchase such a database, and I would suspect that one or more of these would
be in a format that you could import into your ZIPS table. There may even be
a free version from the post office, although I could not find it just now
with a little searching.

Or you could have an IT guru at a transportation company export the entire
database to an Access table for you from something like PCMiler or one of the
other mileage programs.

I am trying to create a mailing address database and would like to have the
option to enter the zip code and the city and state are automatically
populated. Any help is appreciated!