Passing Variables into WHERE Clauses

  • Thread starter Thread starter E Hines
  • Start date Start date
E

E Hines

How do I pass a variable into the WHERE Clause of a SELECT
Statement? I have the following line of code:

.Open "SELECT * FROM ScratchTable WHERE Stock_Sym
= 'FetchStr'", connFCY, adOpenKeyset, adLockOptimistic

All of this works correctly, except the variable FetchStr,
which resolves to a stock symbol, isn't getting passed
correctly. Enclosed in single quotes, as above, makes the
variable treated as a literal (the WHERE looks for
FetchStr in the Stock_Sym field rather than looking for
the stock symbol contained in the variable FetchStr).
Without the single quotes, and I get a missing parameter
error.

Any help would be much appreciated.

Eric Hines
 
Open "SELECT * FROM ScratchTable WHERE Stock_Sym = '" & FetchStr & "'",
connFCY, adOpenKeyset, adLockOptimistic

Exagerrated for clarity, that's

Stock_Sym = ' " & FetchStr & " ' "

as I assume Stock_Sym is a text field.
 
Works like a champ. Yes, Stock_Sym and FetchStr are
strings; I'd leave off the " for numbers.

Thanks.

Eric
 
That's right. And for dates, you'd need to enclose them in ## (and they must
be in mm/dd/yyyy format, regardless of what the short date format has been
set to on the workstation)
 
Back
Top