ACC: Using LIKE with Wildcards May Cause Unexpected Results (200406)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q200406
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

In a Microsoft Access query, if you use the LIKE operator with a wildcard, such as "*" or "?", to search for dates, you may see unexpected results.

CAUSE

The LIKE function is used to compare two strings. It does not inspect the string for any type of date information.

RESOLUTION

Use the comparison operators <, >, <=, =>, <> or the BETWEEN clause when searching for dates. For example, when looking for dates in the month of May 97, use the following syntax:

Orders.OrderDate >= #5/1/1997# AND Orders.OrderDate <= #5/31/1997#

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.
  2. Create a new query in Design view, and close the Show Tables dialog box without adding any tables.
  3. On the View menu, click SQL View and type in the following:

    SELECT Orders.CustomerID, Count(*) AS NumberOfOrders
    FROM Orders
    WHERE (((Orders.OrderDate) BETWEEN #5/1/97# AND #5/31/97#))
    GROUP BY Orders.CustomerID

  4. Save the query as qryTest1 and run it. It returns the expected results.
  5. Create a second query called qryTest2 with the following SQL:

    SELECT Orders.CustomerID, Count(*) AS NumberOfOrders
    FROM Orders
    WHERE (((Orders.OrderDate) LIKE "5/*/97"))
    GROUP BY Orders.CustomerID

    NOTE: The only difference from the SQL above is that the WHERE clause uses a wildcard.

  6. Run qryTest2. You see that it also returns the expected results as in qryTest1.
  7. Now change the SQL in qryTest2 so that the date uses a 4-digit year as in the following statement:

    SELECT Orders.CustomerID, Count(*) AS NumberOfOrders
    FROM Orders
    WHERE (((Orders.OrderDate) LIKE "5/*/1997"))
    GROUP BY Orders.CustomerID

  8. Run the query.

    Note that no rows are returned as no matches are found.

REFERENCES

For more information about using criteria to find certain records, click Microsoft Access Help on the Help menu, type criteria to retrieve records in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:10/22/2003
Keywords:kbprb KB200406
©2003 Microsoft Corporation. All rights reserved.