Home » Category » Microsoft Access

Microsoft Access: ZipCode Lookup and field fill

204| Sun, 01 Jun 2008 13:55:00 GMT| scottwhetsell_a_s_wvsp| Comments (8)
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.

Keywords & Tags: zipcode, lookup, field, fill, microsoft, access

URL: http://database.itags.org/ms-access-database/207240/
 
«« Prev - Next »» 8 helpful answers below.
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 droptable.com
http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1

jahoobobviaaccessmonster_com | Sun, 01 Jun 2008 13:57:00 GMT |

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 droptable.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:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
>

jeffboyce | Sun, 01 Jun 2008 13:58:00 GMT |

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:[vbcol=seagreen]
>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
>[quoted text clipped - 16 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1

jahoobobviaaccessmonster_com | Sun, 01 Jun 2008 13:59:00 GMT |

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 droptable.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:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
>

scottwhetsell_a_s_wvsp | Sun, 01 Jun 2008 14:00:00 GMT |

On Fri, 22 Jun 2007 11:22:00 -0700, Scott Whetsell, A.S. - WVSP
<ScottWhetsellASWVSP...discussions.microsoft.com> 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]

johnw_vinson | Sun, 01 Jun 2008 14:01:00 GMT |

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:[vbcol=seagreen]
>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
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1

jahoobobviaaccessmonster_com | Sun, 01 Jun 2008 14:02:00 GMT |

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 droptable.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:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/access-tablesdbdesign/200706/1
>

scottwhetsell_a_s_wvsp | Sun, 01 Jun 2008 14:03:00 GMT |

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"
<ScottWhetsellASWVSP...discussions.microsoft.com> wrote in message
news:7279BFF5-B5AE-404F-BEBF-A34487177EC6...microsoft.com...[vbcol=seagreen]
>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 droptable.com" wrote:

jeffboyce | Sun, 01 Jun 2008 14:04:00 GMT |

Microsoft Access Hot Answers

Microsoft Access New questions

Microsoft Access Related Categories