SQL Statement in a Form Control

  • Thread starter Thread starter Peter Kurz
  • Start date Start date
P

Peter Kurz

I'm trying to use a SQL statement in a form in order to
display a city name (CITY) based upon a zip code (ZIP) in
the underlying table.

I have a table that links zip codes with cities: Zip-to-
Town.

The SQL statement in the Control Source is the following:

SELECT [Zip-to-Town].City FROM [Zip-to-Town] WHERE [Zip-to-
Town].Zip=[Form].Zip;

The result is always #NAme?

I have tried the usual variants (equal sign before the
statement, parens, with or without the final semicolon,
always with the same result.

Am I missing something obvious? If it's VERY obvious, then
forgive me for wasting your time, but please answer anyway!

Many thanks for your help.

Peter Kurz
My own email, if you care to respond directly, is
(e-mail address removed)
 
Where in the form are you trying to use it. If in code, try something like

strSQL = "SELECT [Zip-to-Town].City FROM [Zip-to-Town] WHERE [Zip-to-Town].Zip=" & Me.Zip
& ";"
 
I'm trying to use a SQL statement in a form in order to
display a city name (CITY) based upon a zip code (ZIP) in
the underlying table.

I have a table that links zip codes with cities: Zip-to-
Town.

The SQL statement in the Control Source is the following:

SELECT [Zip-to-Town].City FROM [Zip-to-Town] WHERE [Zip-to-
Town].Zip=[Form].Zip;

The result is always #NAme?
Click to expand...

**You cannot utilize an SQL statement that way, but you can use the "DLookup()"
function to obtain the same results. Try entering this in the control's "Control
Source" property:

=DLookup("City","[Zip-to-Town]","Zip=""" & Me!Zip & """"
 
Back
Top