Syntax for DAO 'Like' Select Query

Question

I'm sorry for bothering you all for what is probably a really simple problem. I use DAO Select queries fairly frequently but have never needed to filter using 'Like' based on a form field.

The attached code, which should filter based on a combobox (comboRes), throws up a

Syntax error (missing operator) in query expression error.

I'm guessing the error lies in the Like '*' " & strRes & " '*' but I've tried lots of combinations with no joy.

Could someone please straighten out my syntax/code?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb

Dim strRes As String
strRes = Me.comboRes

Set qdf = db.QueryDefs("qryInst")

strSQL = "SELECT tblInst.*, tblInst.ResList " & _
        "FROM tblInst " & _
        "WHERE (((tblInst.ResList) Like '*' " & strRes & " '*'t ));"

qdf.SQL = strSQL

DoCmd.OpenQuery "qryInst"

qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing

Answer 1

You need to concatenate the string variable to the LIKE clause,

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb

Dim strRes As String
strRes = Me.comboRes

Set qdf = db.QueryDefs("qryInst")

strSQL = "SELECT tblInst.*, tblInst.ResList " & _
         "FROM tblInst " & _
         "WHERE tblInst.ResList Like '*" & strRes & "*';"

qdf.SQL = strSQL

DoCmd.OpenQuery "qryInst"

qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing

So if your strRes is Paul, your SQL will translate to.

SELECT tblInst.*, tblInst.ResList FROM tblInst WHERE tblInst.ResList Like '*Paul*';"

Answer 2

Your SQL needs to be changed somewhat. The like operator takes a string, and the wildcard character depends on the ANSI Query Mode - see here - so you may need to use % instead of *. So to match the contents of strRes anywhere in tblInst.ResList, use the following:

"WHERE tblInst.ResList Like '*" & strRes & "*';"

Note that you are vulnerable to SQL injection when you use this style of code.