InStr used in SQL query

[dchow]

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?

 

[Patrick Molloy]

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

 

[Mike NG]

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

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

Change your SQL to

WHERE TaxId LIKE '%PST%'

Well that's what standard SQL is anyway. If that doesn't work, then it
may be * instead of % - I say this because the Like keyword in an If
statements expects *
e.g. If TaxId like '*PST*' Then

 

[dchow]

Thanks. That's what I need.

Change your SQL to

WHERE TaxId LIKE '%PST%'

Well that's what standard SQL is anyway. If that doesn't work, then it
may be * instead of % - I say this because the Like keyword in an If
statements expects *
e.g. If TaxId like '*PST*' Then