ACC2000: Like vs. Equal to (=) with Wildcard Characters in Query Searches (209834)

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 <> operators, 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 returns:

Foos*

But, if your criteria is

Like "Foos*"

your query returns the following four records:

Foos
Foosball
Foos ball
Foos*

The SQL statement using Like for this query is as follows:

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

The SQL statement using = for this query is as follows:

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

REFERENCES

For more information about using wildcards in the Find and Replace dialog boxes, queries, commands, and expressions, click Microsoft Access Help on the Help menu, type about using wildcard characters to search for partial or matching values in the Office Assistant or the Answer Wizard, and then click Search to view the topic.