SQL string

  • Thread starter Thread starter tim johnson
  • Start date Start date
T

tim johnson

I copied the SQL string directly from the Query pane and
need to format it to used in a procedure.

I would like the user to enter at least the first number
of the zip and to get all zip starting with than number.

I an having problem formatting the SQL into text.

Thanks

SELECT tblCityStZip.CityStZipID, tblCityStZip.Zip,
tblCityStZip.State, tblCityStZip.City, tblCityStZip.County
FROM tblCityStZip
WHERE (((tblCityStZip.Zip) Like [Forms]![frmLookupZip]!
[txtSearch] & "*"));
 
Here's a simplified form with the errors corrected.

SELECT *
FROM tblCityStZip
WHERE tblCityStZip.Zip Like
"""" & [Forms]![frmLookupZip]![txtSearch] & "*"""

If the user types 123 in the textbox, the statement becomes:

SELECT *
FROM tblCityStZip
WHERE tblCityStZip.Zip Like "123*"

HTH,
TC
 
TC said:
Here's a simplified form with the errors corrected.

SELECT *
FROM tblCityStZip
WHERE tblCityStZip.Zip Like
"""" & [Forms]![frmLookupZip]![txtSearch] & "*"""

If the user types 123 in the textbox, the statement becomes:

SELECT *
FROM tblCityStZip
WHERE tblCityStZip.Zip Like "123*"
Click to expand...

Looks like I'm going to disagree with you twice in one night, TC.
Although your simplification does make it easier to read, I believe this
is perfectly valid SQL if run from Access:

SELECT *
FROM tblCityStZip
WHERE tblCityStZip.Zip Like
[Forms]![frmLookupZip]![txtSearch] & "*"

Whatever's in the text box is going to be converted to a string if
necessary and concatenated with "*", yielding a string value as the
object of the Like operator.

I think Mr. Johnson is looking for a way to get this SQL statement into
a string variable; I'd suggest something like this:

Dim strSQL As String

strSQL = _
"SELECT * FROM tblCityStZip " & _
"WHERE tblCityStZip.Zip Like " & _
"[Forms]![frmLookupZip]![txtSearch] & '*'"

I've substituted single quotes (') for the original double-quotes around
"*", so as to avoid conflicts with the double-quotes around the string
literal.

Now granted, this won't work if you go to open a recordset from it, á la

Set rs = CurrentDb.OpenRecordset(strSQL)

That's because the form!control reference will be interpreted as an
unresolved parameter. In that case, one *would* want to use your
revision of the SQL statement, as in

strSQL = _
"SELECT * FROM tblCityStZip " & _
"WHERE tblCityStZip.Zip Like " & _
"""" & [Forms]![frmLookupZip]![txtSearch] & "*"""

That way, as you said, the control's value will be embedded in the
string. But I wouldn't describe the original SQL as in error.
 
Back
Top