Passing text parameters in T-SQL requires the text to be
enclosed in single or double quotes
You haven't shown us the full code, but to force a double
quote into a string you need to do it the way you showed,
ie
Sub test2()
Dim mytext As String, myValue As String
myValue = "RED"
mytext = "WHERE [MyField] = """ & myValue & """ "
Debug.Print mytext
End Sub
generates the following
WHERE [MyField] = "RED"

without quotes generates
WHERE [MyField] = RED
....which generates a TSQL error

You could use single quotes, which I do, simply to make
the code easier to read...
Sub test3()
Dim mytext As String, myValue As String
myValue = "RED"
mytext = "WHERE [MyField] = '" & myValue & "'"
Debug.Print mytext
End Sub
which generates
WHERE [MyField] = 'RED'
Which is how you'd most likely write it anyway in SQL
Query Analyser

Note that Numbers are not enclosed
Sub test4()
Dim mytext As String, myValue As String, myLimit As
Long
myValue = "RED"
myLimit = 8
mytext = "WHERE [MyField] = '" & myValue & "' AND
[Stock] <=" & myLimit & ";"
Debug.Print mytext
End Sub
generating
WHERE [MyField] = 'RED' AND [Stock] <=8;

Hope this clears it up.
Patrick Molloy
Microsoft xcel MVP


-----Original Message-----
I would like to get all records with TaxID
contains "PST". I used the
following WHERE clause but kept on getting SQL string
error.

WHERE (InStr(TaxID, ""PST"") < 0)

If I changed it to
WHERE (Right(TaxID, 3) = ""PST"")

then it worked.

What was wrong with my InStr?
.