Trouble using "Like" in SQL Statement in VBA for Access 2013

Question

I've read through hundreds of questions and answers, but I just can't seem to get this to work. I'm attempting to use this SQL statement to set the rowsource for a listbox...

If searchOpt = 1 Then
    sqlStr = "SELECT tblEquipment.anumEquipID," & _
    "   tblEquipment.strPCN," & _
    "   tblEquipment.strDescription," & _
    "   tblEquipment.strOwnerDepartment," & _
    "   tblEquipment.strPrimaryID," & _
    "   tblEquipment.strSecondaryID," & _
    "   tblEquipment.strNote," & _
    "   tblEquipment.ynCapitalItem," & _
    "   tblEquipment.strLocation," & _
    "   tblContacts.strPrimaryContact," & _
    "   tblContacts.strPrimaryPhone" & _
    "   FROM tblContacts RIGHT JOIN tblEquipment ON tblContacts.anumID = tblEquipment.strPrimaryID" & _
    "   WHERE (((tblEquipment.strDescription) Like " * " & [Forms]![frmSearch]![strSearchBox] & " * "));"

I keep getting a "type Mismatch" error using the above. I've tried changing the quotes and changing the * to %, where I either get a "Syntax" error or no error but the listbox does not populate. By the way this code is tied to a button that is pressed to search the text of a data field "strDescription" using an unbound text box on the form.

I would really appreciate any help you can offer. Thanks

Mikegrann Mikegrann · Accepted Answer · 2016-08-11T17:05:34

* also means "multiply two things" (eg x * y) so your misformatted code is giving you a type error when you try to multiply two strings (because, of course, that makes no sense!)

What you really want is for that last line to encapsulate the * instead of being separate strings with a * operation between them. Replace it with:

"   WHERE (((tblEquipment.strDescription) Like ""*"" & [Forms]![frmSearch]![strSearchBox] & ""*""));"

(We use two double-quotes as an escape to get a single double-quote in the output - just trust me, it works.)

Trouble using "Like" in SQL Statement in VBA for Access 2013

1 Answers