Querying Dates does not work

[machumpion]

G'day ! In query design, whenever I try to pull specific data from a Date field under 'Criteria' (i.e. >#2016-01-01#, 'after January 1, 2016'), it seems to simply ignore my date criteria and return data for the entire range of dates in my table. What am I doing wrong?

 

[plog]

1. Are you sure its a date field?

2. Do you have other criteria?

3. Can you post your full SQL?

 

[machumpion]

TRANSFORM Sum(Orders.Revenue) AS SumOfRevenue
SELECT Stores.StoreName
FROM Stores INNER JOIN Orders ON Stores.StoreName = Orders.Store
WHERE (((Format([OrderMonth],"yyyy-mm"))>#1/1/2016#))
GROUP BY Stores.StoreName
PIVOT Format([OrderMonth],"yyyy-mm");

The field is 'Date/Time', even without additional criterion it seems to still return everything.

 

[plog]

It's no longer a Date/Time when you apply Format to it--it becomes a string. String comparisons, date comparisons and number comparisons use different logic. No need to use Format in the WHERE clause.