SQL query with like does not accept wild cards

Hi All

I seem to be experiencing a strange problem. I am using ADODB to read an Access Database on an IIS server (actually I'm testing it with PWS). When I open a recordset with an sql query containing a 'where x like y' clause I am unable to use wild cards. If I use the * wild card then I get no records returned. A code segment is as below

<%
     whereClause = "WHERE Material Like '*Silk*'"
     
     Call OpenConnection2(objConn,objRS)
     strSQL = "SELECT * FROM [Stock List] " & whereClause & " order by [Stock No];"
     Response.Write "<BR>" & strSQL
     objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly %>

If however I remove the wild card a get the expected results.

Anybody got any aideas as to what I am doing wrong?

Many thanks

Steve
spwigginsAsked:
Who is Participating?
 
Dave_GreeneConnect With a Mentor Commented:
Use Like %Silk%

% instead of *
0
 
John844Commented:
access has problems with the wildcards being on both sides

try removing the wildcard from one of the sides

if this works for you then change your where statement to something like
where (field like '*test' or field like 'test*')
0
 
John844Commented:
I think that access will generate an error when you use %.  SQL needs to use the % as wildcard character.  Access uses * for the wildcard character.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
John844Commented:
example with your code
whereClause = "WHERE (Material Like '*Silk' or Material LIKE 'Silk*')"
0
 
Dave_GreeneCommented:
Trust me use the '%' Character...

Cheers!
0
 
John844Commented:
what version do you get the % character to work on.  Just tested on Access97 and it does not work.  I was able to get it to work using *.  I was also able to use the * on both sides of the word like '*test*'.
0
 
Dave_GreeneCommented:
Access 97 & 2000.  Here is an example query.

        strQry = "SELECT * FROM tblIssues WHERE System LIKE '" & Trim(txtADVsearch.Text) & "%' " & _
            "OR OS LIKE '" & Trim(txtADVsearch.Text) & "%' OR Application LIKE '" & Trim(txtADVsearch.Text) & "%' " & _
            "OR Hardware LIKE '" & Trim(txtADVsearch.Text) & "%' OR Peripherals LIKE '" & Trim(txtADVsearch.Text) & "%' " & _
            "OR Other LIKE '" & Trim(txtADVsearch.Text) & "%'"

But the problem is that you cannot do a full text value searching with Access.  It is a new feature in SQL2000
0
 
Dave_GreeneCommented:
John844,

Are you making the query from inside access?  If so then the * will work.  If you are using ODBC, the * won't work
0
 
spwigginsAuthor Commented:
Thanks Dave for a speedy response

Steve
0
 
John844Commented:
thanks for the info Dave.  I was running the query from inside access.  I did have the same problems spwiggins is describing and Fixed it using the method '*test' or 'test*'.  I had not used SQL back then though(been a while).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.