Access Queries with “Like” Criteria Not Working

If your data source is a Query in an Access database, using “Like” in the criteria will not limit the records properly. The problem occurs because ADO and DAO use different wildcard characters (% vs *).

To work around this problem, use one of the following methods.

Method 1

You can rewrite the query to replace the Jet-specific wildcard character with the ANSI 92 wildcard character. For example, the original query may look similar to this with the * wildcard character:

SELECT Customers.CustomerID
FROM Customers
WHERE (Customers.CustomerID Like "A*");

You can rewrite the query as follows:

SELECT Customers.CustomerID
FROM Customers
WHERE (Customers.CustomerID Like "A%");

Note If the Access database is not configured for ANSI 92 compatibility, when you run the rewritten query from Access, no data is returned. However, the imported data that is based on the rewritten query returns the expected results.

Method 2

You can rewrite the query so that the criterion of the query does not contain wildcard characters. For example, the original query may look similar to this with the * wildcard character:

SELECT Customers.CustomerID
FROM Customers
WHERE (Customers.CustomerID Like "A*");

You can rewrite the query as follows:

SELECT Customers.CustomerID
FROM Customers
WHERE Left(CustomerID, 1) = "A";