CODEWITHSUNDEEP
sqlms-access
Danchat
Danchat

Reputation: 156

How to build proper Access SQL LIKE operator expression?

I'm attempting to have a user search through a table in Microsoft Access 2010, but the SQL command isn't working. The command that loads and refreshes the table is this:

SELECT Equipment.equipmentID, Equipment.equipmentName, Equipment.model, 
Equipment.make, Equipment.equipmentLocation FROM Equipment ORDER BY Equipment.equipmentName;

This works, but when I try to use a variable (or any normal criteria):

searchItem = Me.searchBox.Value
Me.List64.RowSource = "SELECT Equipment.equipmentID, Equipment.equipmentName,
Equipment.model, Equipment.make, Equipment.equipmentLocation FROM Equipment 
WHERE Equipment.equipmentName LIKE '%searchItem%' ORDER BY Equipment.equipmentName;"

I've also tried something like "%10%" instead of the searchItem variable, but the command has the table come up blank with no errors. I suspect the problem is with the Equipment.eqiupmentName as the column name, but I can't quite figure out what's wrong here.

Here's a quick look at what the table looks like:

test

Upvotes: 0

Views: 4475

Answers (2)

C Perkins
C Perkins

Reputation: 3886

Try this:

Me.List64.RowSource = & _
    "SELECT Equipment.equipmentID, Equipment.equipmentName," & _
        " Equipment.model, Equipment.make, Equipment.equipmentLocation FROM Equipment" & _
    " WHERE Equipment.equipmentName LIKE '*" & searchItem & "*'" & _
    " ORDER BY Equipment.equipmentName;"

  • User rjt011000 has a valid solution, but I recommend using & for string concatenation in VBA (and Access). For an explanation of + and & see this thread.

  • Access will not recognize or substitute VBA variables inside an SQL statement. Furthermore, the LIKE operator is fed an SQL string value in this case (inside single quotes... which are inside the double quotes), so even if a VBA variable could be referenced directly inside SQL, Access does not interpret any such thing inside a string value.

  • Regarding the Access SQL LIKE operator, the multi-character matching pattern is * rather than %. Access also recognizes the operator ALIKE which does indeed honor the ANSI pattern %. See LIKE operator docs and this thread regarding ALIKE.

    • To be more thorough, the string delimiters and LIKE pattern-matching character should be escaped if you don't want the user inadvertently injecting invalid characters that cause errors in the SQL. Following is an example of escaping a couple of them. There are more elegant ways to handle this for all special characters, but the code and technique are beyond the scope of this answer.
 ...'" & Replace(Replace(searchItem, "*", "[*]"), "'", "''") & "'...

  • For the record, although Access SQL will not substitute a VBA variable, it will recognize and call a public VBA function. Normally such a public function must be defined in a normal module, but in context of a form's Record Source query, a form-module method can sometimes be called.

  • One last technique... It is possible to reference a form control's value directly in SQL. This can be very convenient and reduce extra code, but there are a couple caveats:

    • The form must of course be open, otherwise Access will interpret the reference as an unknown parameter and display a prompt. This will of course not be a problem if the SQL is always in context of the same form.
    • Access will sometimes automatically refresh the query when such a referenced control is changed, but it is not always guaranteed. The "timing" of automatic refreshes might not be immediately intuitive. You can call the Refresh method on the control or subform from various form events to force the query to refresh after the value is changed.
    • Notice that in the following example, the string concatenation is inside the VBA string, so that the concatenation actually happens in context of SQL and not beforehand like in the first code snippet. There is no problem with this, just something to consider since this entire answer revolves around proper string interpretation and concatenation.
      • But really, the same concern exists for un-escaped pattern-matching characters in the user text. Rather than making the SQL text long and ugly with calls to Replace(), instead create a custom function (e.g. EscapePattern()) that does this for any text and then wrap the control reference with that function. The example does this, although I don't include the code for the special function. Such a function could also be used in the first VBA code snippet to simplify building the SQL text.
Me.List64.RowSource = & _
  "SELECT Equipment.equipmentID, Equipment.equipmentName," & _
      " Equipment.model, Equipment.make, Equipment.equipmentLocation FROM Equipment" & _
  " WHERE Equipment.equipmentName LIKE ('*' & EscapePattern(Forms![Form Name]![Control Name]) & '*')" & _
  " ORDER BY Equipment.equipmentName;"
  • There is always more! Did you see the VBA line continuation in my example? It makes the SQL text much easier to view within VBA editor.

Upvotes: 3

rjt011000
rjt011000

Reputation: 112

I suspect you are not setting your searchItem variable correctly in the SQL string. I am not too familiar with access string concatenation but try separate the searchItem out of the SQL string and then checking if your RowSource has the value you suspect.

Me.List64.RowSource = "SELECT Equipment.equipmentID, Equipment.equipmentName,
Equipment.model, Equipment.make, Equipment.equipmentLocation FROM Equipment 
WHERE Equipment.equipmentName LIKE '%" + searchItem + "%' ORDER BY Equipment.equipmentName;"

Upvotes: 0

Related Questions