ACC2000: Using LIKE with Wildcards May Cause Unexpected Results

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:

   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. Note that 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. Change the SQL in qryTest1 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.