If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003
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! |
#2
|
|||
|
|||
Access 2003
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" wrote: 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! |
#3
|
|||
|
|||
Access 2003
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. "Brian" wrote: 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" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|