Zip to look up city state

[mehronx]

Could someone point me to some info on setting up a city state lookup based on an entered zip code?

Are there previous threads dedicated to this topic? I didn't find any initially.

[lrcarey]

Hi,

I'm not one of the experts, but here's what I did:

Create a separate file that has just city, state, zip code (and I also have county). Then create a relationship in your main file that matches the zip code entered during data entry to the zip code file. The city and state can be calculated through that relationship. The main drawback I found was that in my area, several towns are covered by the same zip code. In order to give the user the option to choose a town, I made the "town" field a drop down list that is brought in through the relationship you've created.

Hope that helps!

Leah

[mehronx]

That sounds good. I'll try that. Did you have to buy the list of zip codes?

[lrcarey]

I don't know how widespread your needs are. I only needed zip codes for three states (small ones at that!) so I found listings by county on the web and brought them into an excel spreadsheet and then imported them into the database. It was a bit time intensive, but it worked just fine - the only snag was when I copied the tables of zip codes (off the source of the web page on which they appeared), they showed up without the leading zero in the Excel spreadsheet. So then I wrote a little formula to add the leading zero back in. I'm sure there was an easier way to do it, but that's what I came up with.

Also (I forgot to tell you this before) in the main data entry document, I made a field that pulled just the first five digits of a zip code (left(zip code, 5)) so if someone entered a nine digit zip code the relationship was only keying off the first five digits.

Good luck!

Leah

[kenneth2k1]

I have this file that I think was made by Keith Kiel (or someone over at Mac/Windows Academy). I got it from the FM Boot Camp about a year ago. It should be OK to give to you because it was given to me, you just can't sell it.

There is a Zip field you can find on and it will give you the state and city. Not all zips are in there, but I have found that most of them are. Unfortunately it is too big to post here (I even tried to zip it) so email me if you want it.

Ken