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

ID: Q88166

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

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