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
|
|||
|
|||
ZipCode Lookup and field fill
I would like to be able to type a zip code into a form, and have it
automatically return the City and State into their respectiver fields, or enter the city and state and have it return the zip code. I have one table which contains Zip, City, and State fields which is the master listing for the Zip Codes. The other table is where I would like the data to be stored after it is entered and looked up. This table is basically a contacts table with name, address, phone, etc. I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. |
#2
|
|||
|
|||
ZipCode Lookup and field fill
You don't have to store the city and state in the other table as you can
always find them through the ZIP. Base all your lookup forms and reports (mailing labels, etc.) on a query based on both tables with the link between ZIPs. Scott Whetsell, A.S. - WVSP wrote: I would like to be able to type a zip code into a form, and have it automatically return the City and State into their respectiver fields, or enter the city and state and have it return the zip code. I have one table which contains Zip, City, and State fields which is the master listing for the Zip Codes. The other table is where I would like the data to be stored after it is entered and looked up. This table is basically a contacts table with name, address, phone, etc. I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
#3
|
|||
|
|||
ZipCode Lookup and field fill
This approach doesn't always work.
We have a pair of adjacent municipalities in my area that share a single zip code. Storing the zip code only would NOT resolve which city. Regards Jeff Boyce Microsoft Office/Access MVP "jahoobob via AccessMonster.com" u12179@uwe wrote in message news:7414d3fc4ec4b@uwe... You don't have to store the city and state in the other table as you can always find them through the ZIP. Base all your lookup forms and reports (mailing labels, etc.) on a query based on both tables with the link between ZIPs. Scott Whetsell, A.S. - WVSP wrote: I would like to be able to type a zip code into a form, and have it automatically return the City and State into their respectiver fields, or enter the city and state and have it return the zip code. I have one table which contains Zip, City, and State fields which is the master listing for the Zip Codes. The other table is where I would like the data to be stored after it is entered and looked up. This table is basically a contacts table with name, address, phone, etc. I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
#4
|
|||
|
|||
ZipCode Lookup and field fill
Yes, a better way would be to index all the cities ZIPs and store the index
in the main table but from what Scott stated, that he will type a ZIP and a city and state will be stored, indicates he has only one-to-one city to ZIP. I used to live in Pace, FL 32571, however, if you put 32571 in any on line registration it will show up as Milton, FL. It really doesn't matter what the city, the PO delivers by ZIP. I haven't tried it but I believe you could put Timbuktu as the city and 20500 as the ZIP and your mail will get to the White House. Jeff Boyce wrote: This approach doesn't always work. We have a pair of adjacent municipalities in my area that share a single zip code. Storing the zip code only would NOT resolve which city. Regards Jeff Boyce Microsoft Office/Access MVP You don't have to store the city and state in the other table as you can always find them through the ZIP. Base all your lookup forms and reports [quoted text clipped - 16 lines] I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
#5
|
|||
|
|||
ZipCode Lookup and field fill
In reference to the comment that several smaller towns may fall under one
zipcode reaffirms my need to be able to store the city/state/zip in the main table so I can change the city in such cases. Thanks "jahoobob via AccessMonster.com" wrote: Yes, a better way would be to index all the cities ZIPs and store the index in the main table but from what Scott stated, that he will type a ZIP and a city and state will be stored, indicates he has only one-to-one city to ZIP. I used to live in Pace, FL 32571, however, if you put 32571 in any on line registration it will show up as Milton, FL. It really doesn't matter what the city, the PO delivers by ZIP. I haven't tried it but I believe you could put Timbuktu as the city and 20500 as the ZIP and your mail will get to the White House. Jeff Boyce wrote: This approach doesn't always work. We have a pair of adjacent municipalities in my area that share a single zip code. Storing the zip code only would NOT resolve which city. Regards Jeff Boyce Microsoft Office/Access MVP You don't have to store the city and state in the other table as you can always find them through the ZIP. Base all your lookup forms and reports [quoted text clipped - 16 lines] I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
#6
|
|||
|
|||
ZipCode Lookup and field fill
On Fri, 22 Jun 2007 11:22:00 -0700, Scott Whetsell, A.S. - WVSP
wrote: In reference to the comment that several smaller towns may fall under one zipcode reaffirms my need to be able to store the city/state/zip in the main table so I can change the city in such cases. That's certainly how I do it. Zip to City is in reality a many to many relationship: one zip can cover several cities and many larger cities have multiple zips. If you go to the USPS Zipcode directory online http://zip4.usps.com/zip4/citytown.jsp you will see that multicity zips have a "preferred city" but if the zip is correct it will be delivered whichever city is named; and depending on the nature of the mail and the feelings of the recipient, it might be important to use the actual city where the person lives rather than the name of the city where their post office stands. John W. Vinson [MVP] |
#7
|
|||
|
|||
ZipCode Lookup and field fill
If you type in a ZIP (as you say) and you have multiple cities for any one
ZIP the first city will always be the one you get. Scott Whetsell, A.S. - WVSP wrote: In reference to the comment that several smaller towns may fall under one zipcode reaffirms my need to be able to store the city/state/zip in the main table so I can change the city in such cases. Thanks Yes, a better way would be to index all the cities ZIPs and store the index in the main table but from what Scott stated, that he will type a ZIP and a [quoted text clipped - 20 lines] I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
#8
|
|||
|
|||
ZipCode Lookup and field fill
I understand that it will pick the first match, which in most cases will be
the one I need. However, the problem at hand is how to get it to populate the fields with the information in the first place. "jahoobob via AccessMonster.com" wrote: If you type in a ZIP (as you say) and you have multiple cities for any one ZIP the first city will always be the one you get. Scott Whetsell, A.S. - WVSP wrote: In reference to the comment that several smaller towns may fall under one zipcode reaffirms my need to be able to store the city/state/zip in the main table so I can change the city in such cases. Thanks Yes, a better way would be to index all the cities ZIPs and store the index in the main table but from what Scott stated, that he will type a ZIP and a [quoted text clipped - 20 lines] I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
#9
|
|||
|
|||
ZipCode Lookup and field fill
Scott In the AfterUpdate event of the combobox, add something like: Me!txtCity = Me!cboZip.Column(1) Me!txtState = Me!cboZip.Column(2) ... where the SECOND field in your combobox's quey is the City and the THIRD is your State (.Column() is zero-based). Regards Jeff Boyce Microsoft Office/Access MVP "Scott Whetsell, A.S. - WVSP" wrote in message ... I understand that it will pick the first match, which in most cases will be the one I need. However, the problem at hand is how to get it to populate the fields with the information in the first place. "jahoobob via AccessMonster.com" wrote: If you type in a ZIP (as you say) and you have multiple cities for any one ZIP the first city will always be the one you get. Scott Whetsell, A.S. - WVSP wrote: In reference to the comment that several smaller towns may fall under one zipcode reaffirms my need to be able to store the city/state/zip in the main table so I can change the city in such cases. Thanks Yes, a better way would be to index all the cities ZIPs and store the index in the main table but from what Scott stated, that he will type a ZIP and a [quoted text clipped - 20 lines] I have been trying this for about two weeks and can't get it to work. Any help is appreciated, thanks in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200706/1 |
Thread Tools | |
Display Modes | |
|
|