SQL Statement in a Form Control

[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)

 

[Wayne Morgan]

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
& ";"

 

[Bruce M. Thompson]

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?

**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 & """"