ACC: Like vs. = with Wildcard Characters in Query Searches (88166)

SUMMARY

When you use wildcards (for example, * or ?) to search for a string, you must use Like or Not Like in the Criteria field, rather than the Equal to (=) or Not Equal to (<>) operators. When you use the = or <> operator, Microsoft Access treats them as actual characters to search for.

MORE INFORMATION

If you have a table with one column called Col_Name and the following four records

Foos
Foosball
Foos ball
Foos*

and you create a query based on the table with the criteria

="Foos*"

your query will return:

Foos*

But, if your criteria is

Like "Foos*"

your query will return the following four records:

Foos
Foosball
Foos ball
Foos*

The SQL statements built by Microsoft Access for these queries are as follows.

The SQL statement using Like is:

SELECT DISTINCTROW tablename.Col_Name
FROM [tablename]
WHERE ((tablename.Col_Name Like "foos*"));

The SQL statement using = is:

SELECT DISTINCTROW tablename.Col_Name
FROM [tablename]
WHERE ((tablename.Col_Name="foos*"));