City,State Lookup From ZipCode

[uncchcs]

I have a form where the user enters a zip code and the city and state is automatically filled in. I am using the following code:

Private Sub ZipCode_Exit(Cancel As Integer)
Me!State = DLookup("State", "tblZipCode", "ZipCode = " & "'" & Me!ZipCode & "'&quot
Me!City = DLookup("City", "tblZipCode", "ZipCode = " & "'" & Me!ZipCode & "'&quot
End Sub

The problem my users have told me about is that either the zip code is not found or they want enter to a zip code that corresponds to multiple cities. There are about 30,000 US zip codes that correspond to more than one city. What I want to do is if the zip code is not found or a zip code has more than one city, prompt the user to either add the zip code or show them a listbox with the choices of the multiple zip codes with cities and allow them to choose which one then passing the values to the main form. I know I will have to alter my zip code table because currently my zip code has the following fields:
Zip Code* (Primary Key)
City
State
I will have to change it to something like:
ID* (Primary Key)
Zip Code
City
State

I would appreciate any assistance.

Jason

 

[LarryDeLaruelle]

Jason:

I don't think your users have it correct. There are cities which have multiple zipcodes but I have never heard of a zipcode that corresponds to more than one city.

My zipcode table has a little over 42K entries (probably a little out of date) so I can't imagine that 30K zipcodes are for multiple cities.

I use ZipCode as the primary key as you have done and have not had any complaints for the user community.

I would suggest talking to your users again. My guess is that they were not able to clearly describe the problem/s they are encountering. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[MichaelRed]

Hmmmmmmmmmmm,

Larry - I am pretty sure there are a FEW sip codes which cross geo-political boundaries, but these are mostly in rural areas - and they certainly do not constitute a majority of the zip codes in use.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JohnYingling]

We have a few in Washington D.C where various Zip Codes straddle the D.C. MD boundary. The Zip Ciode designates the local Post Office but the Post Office can deliver to more than one political jurisdiction.

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[LarryDeLaruelle]

Interesting. Learn something new every day.

In the interests of zip code trivia would you happen to know which zip codes refer to multiple cities?

May come in handy on Jeopardy some day. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[uncchcs]

Someone pointed me to a zip code table where there are about 32,000 zip codes that correspond to more than one city. I ran an append query and removed the duplicate zip codes and checked a few of the duplicates with USPS and indeed there are a good many that do correspond to muliple cities.

Possibly I could removed the duplicate zip codes and allow users to add their own in the case where a zip code has multiple cities. But I would rather give them the option of choosing which city,state,zip in the case of duplicate zipcodes.

 

[uncchcs]

Just picking one for example:
The zip code 78209 for example, corresponds to the following cities:
San Antonio, TX
Terrell Hills, TX
Alamo Heights, TX

And the zip code, 28621:
Austin, NC
Benham, NC
Burch, NC
North Elkin, NC
Pleasant Hill, NC

My zip code table initially has 78,000 zip codes, upon removing the duplicates, it is left with about 43,000.

 

[raskew]

Believe what you're looking at is little communities, suburbs, etc., that don't have a post office, thus no zip code. Thus, they rely on a nearby larger city.

Suspect you'll find that if you addressed a letter to:

123 E. Cactus Lane., San Antonio, TX 78209

And another to:

123 E. Cactus Lane, Terrell Heights, TX 78209

...both letters would arrive at the same address.

My experience with downloaded ZipCode tables is that the ones that list multiple cities for a particular Zip have been updated by the user. Those that use a unique Zip corresponding with just one city seem to come from the USPS.

Maybe someone with real USPS expertise can set us straight.

 

[uncchcs]

I suppose I need to find another zip code table...
Does anyone know where I can get a updated table with no duplicates?

 

[JohnYingling]

Raskew,

Zip Codes identify the Post Office that delivers your mail. If someone can receive mail, they have a Zip Code. One City may have multiple Zip codes and one Zip Code may have multiple cities. Think about it. Most of the country is exurban. One Post Office serves several towns. You may have a problem switching the City names (Town) when the street is Main Street.

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[uncchcs]

Here is a link to a tool that does almost exactly what I want to do:

http://secure.necaweb.com/caryp-cgi/hazel.cgi?action=serve&item=zipcodelookup.htm

Download the demo and have a look and let's see if we can come close if anyone can help. Why don't I just purchase the tool? Well, I think $679 (developer version)is a little much for a zip code tool.
If you download the tool, check out the zip code table (only has about 30 zip codes, but there are multiple cities for one zip code) enter on of those zip codes and notice what happens.

 

[MichaelRed]

Why doesn't a simple select query as the source for the list box work? Add the code to pronpt for the additional entry when the query returns no records.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[BanditWk]

I ran into the same thing - just FYI, fellow programmers... I get an updated ZipCode table on a monthly basis... When this issue first reared it's ugly head, I called the US Postal Service and discovered that there is NO SUCH THING as one zip code having multiple cities... What is often the case is that you have a person living in a city and wants to basically be associated with another region that the zip code covers, however, in the USPS eyes, it's the same region... A good example would be, 89113 might be considered Spring Valley, NV however, it falls into the same jurisdiction and mailing region as Las Vegas, NV - so a user in Spring Valley may associate themselves with "Spring Valley" but in essence, it's Las Vegas...
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)