City to Zip Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a form I want to create an expression so that if field (City)="Cambridge"
then field Zip Code = "12345". I don't know how to create it properly?
Help!
 
Nick said:
On a form I want to create an expression so that if field
(City)="Cambridge" then field Zip Code = "12345". I don't know how
to create it properly? Help!
Click to expand...

You can do this pretty easily by looking up in a table that contains
fields for City and ZipCode -- your lookup could be done either by a
DLookup expression or by basing the form on an "autolookup query".
HOWEVER, is this only for specific cities? If not, how will you handle
the many cities that comprise multiple zip codes?
 
I'll assume for description's sake that the city will be in a combo box
control and the zip code will be in a text box (either one could be either
control; it's coded basically the same way).

I assume you have a table that lists all the cities and their corresponding
zip codes. We'll call this tblCities. Your city combo box (or whatever it
happens to be) should have a row source something like:

SELECT [tblCities].[CityName] FROM [tblCities]

Since the zip code depends on the city, your zip code text box should have a
row source that takes the value in the city box into account, like so:

SELECT [tblCities].[ZipCode] FROM [tblCities] WHERE [tblCities].[CityName] =
Me.CityComboBox

This select statement chooses the zip code that goes with the city that is
listed in the city box (I called it CityComboBox).

The last thing you might need to do is make sure that the zip code box gets
updated with the right zip code if the city box changes value. We can use
the CityComboBox's "After Update" event to trigger our code, which is simply:

Me.ZipCodeTextBox.Requery

Hope that helps. Good luck.

-ndalton
 
Thanks for your help. I'm have some syntax problems, I'm getting an error:
"The syntax of the subquery in this expression is incorrect. Check the
syntax and enclose the subquery in parenthesis." Any ideas? I appreciate
any help you may be able to provide. Thanks again.

ndalton said:
I'll assume for description's sake that the city will be in a combo box
control and the zip code will be in a text box (either one could be either
control; it's coded basically the same way).

I assume you have a table that lists all the cities and their corresponding
zip codes. We'll call this tblCities. Your city combo box (or whatever it
happens to be) should have a row source something like:

SELECT [tblCities].[CityName] FROM [tblCities]

Since the zip code depends on the city, your zip code text box should have a
row source that takes the value in the city box into account, like so:

SELECT [tblCities].[ZipCode] FROM [tblCities] WHERE [tblCities].[CityName] =
Me.CityComboBox

This select statement chooses the zip code that goes with the city that is
listed in the city box (I called it CityComboBox).

The last thing you might need to do is make sure that the zip code box gets
updated with the right zip code if the city box changes value. We can use
the CityComboBox's "After Update" event to trigger our code, which is simply:

Me.ZipCodeTextBox.Requery

Hope that helps. Good luck.

-ndalton

Nick said:
On a form I want to create an expression so that if field (City)="Cambridge"
then field Zip Code = "12345". I don't know how to create it properly?
Help!
Click to expand...
Click to expand...
 
I sure hope you either have the city name with the state included
(which is NOT a good idea) or you are showing the State somewhere.

I live in Grand Junction, Colorado. But I know that there is at least a
Grand Junction, IA also. Without the state showing I would have no idea
which zip to pick if the state was not showing (or I didn't know one of
them ahead of time).

Ron
 
I have a table with City and corresponding Zip. This database is used to
serve a very local need; all in one county. Thanks.
 
I just wanted to raise the flag just in case.

Some individuals are not aware of the larger picture once they get out
of thier local county. Looks as if you have thought of it, and that is
all I was trying to bring up.

Have a great 4th.

Ron
 
Back
Top