ACC2000: Using LIKE with Wildcards May Cause Unexpected Results

ID: Q199163


The information in this article applies to:


SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

In a Microsoft Access query, if you use the LIKE operator with a wildcard 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. Open a new query in Design view, and add no tables.


  3. On the View menu, click SQL View, and type the following SQL:


  4. SELECT Orders.CustomerID, Count(*) AS NumberOfOrders
    FROM Orders
    WHERE (((Orders.OrderDate) BETWEEN #5/1/97# AND #5/31/97#))
    GROUP BY Orders.CustomerID
  5. Save the query as qryTest1 and run it. Note that it returns the expected results.


  6. Create a second query called qryTest2 with the following SQL:


  7. 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.

  8. Run qryTest2. You see that it also returns the expected results as in qryTest1.


  9. Change the SQL in qryTest1 so that the date uses a 4-digit year as in the following statement:


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


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

Additional query words: prb


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999