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

First off, best practice dictates you should be using a prepared statement (example [here](http://stackoverflow.com/a/6572841/410342)). Second, that SQL query is fairly wonky; what exactly is the purpose of your query? — Shotgun Ninja, Jun 16 '15 at 14:00
Thanks for getting back to me. "pretty wonky" describes most of my coding. Sorry for being a complete idiot but I've looked at your example and am still not sure what a "prepared statement" is. I'll have to spend more time on learning to code properly. — user3531420, Jun 16 '15 at 14:55
A Prepared Statement is just a query that gets pre-built as a string with little replacement blocks that get filled in by a method call (ie. `setParameter(block-index, value)`) after the query string is compiled into an object. They handle value checking and object literal formatting (ie. correct quotes), and help prevent SQL injection. — Shotgun Ninja, Jun 16 '15 at 14:59
The answer I linked to above shows how to change a non-prepared query (in the question) into a prepared statement. That's another improvement for a different day; for now, work on getting your `LIKE` clause to work, and *then* you can look into making it a Prepared Statement. — Shotgun Ninja, Jun 16 '15 at 15:03
That's great. Now I've got the Query working with the help of PaulFrancis I can look into Prepared Statements, your comments have given me a great start. Thanks again. — user3531420, Jun 17 '15 at 07:48

score 0 · Answer 1 · edited May 23 '17 at 11:58

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.

score 0 · Accepted Answer · answered Jun 16 '15 at 13:48

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*';"

That worked a treat Paul. I knew it was my lack of knowledge regarding syntax that was tripping me up. Thanks a lot for your time on this — user3531420, Jun 16 '15 at 14:58

Syntax for DAO 'Like' Select Query

2 Answers2