Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr used in SQL query
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr used in SQL query
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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr used in SQL query
On Fri, 18 Jul 2003 at 02:50:55, dchow (dchow )
wrote: 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 -- Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
InStr used in SQL query
Thanks. That's what I need.
On Fri, 18 Jul 2003 23:18:57 +0100, Mike NG wrote: On Fri, 18 Jul 2003 at 02:50:55, dchow (dchow ) wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use InStr function in formula? | Excel Worksheet Functions | |||
is there an equal fxn for 'InStr' in excel. Not Find or Search | Excel Worksheet Functions | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Help with VBA InStr() function | Excel Discussion (Misc queries) | |||
Where is the Instr() function in Excel 2003? | Excel Discussion (Misc queries) |