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

ID: Q88166


The information in this article applies to:


SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

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


Keywords          : kbusage QryOthr 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: March 10, 1999