CODEWITHSUNDEEP
datems-access
kneidels
kneidels

Reputation: 914

Access date filter

I have a table that lists orders received.

I am running a report, to see how many orders receiver prior to D-Day. So for example, I would want to see:

D Day-5 days: 8 orders received in total 
D Day-4 days: 12 orders received in total 
D Day-3 days: 20 orders received in total 
D Day-2 days: 33 orders received in total 
D Day-1 days: 55 orders received in total

This is the query I am using: 

SELECT * FROM recipients 
WHERE  
orderDate > #01/01/2017# 
AND orderDate < #10/02/2017#   -- (Where 10/2/17 is already X days prior to D Day)

But the query is running and giving me all orders received to date (which is 26/2/2017).

The correct answer should be zero (no orders received before the 10th of Feb), but instead - its showing me 200 + records, where i can see that the orderDate field is after the 10th of Feb.

Appreciate your input!

Upvotes: 0

Views: 46

Answers (2)

Gustav
Gustav

Reputation: 55831

Access SQL doesn't expect the format dd/mm/yyyy but mm/dd/yyyy or yyyy/mm/dd for string expression for a date value (which by itself carries no format). Thus, 10/02/2017 is read as 2017-10-02, the second day of October.

So:

SELECT * FROM recipients 
WHERE  
orderDate > #01/01/2017# 
AND orderDate < #02/10/2017#

or, the generic method as you rarely has fixed date values:

PARAMETERS DateFrom DateTime, DateTo DateTime;
SELECT * FROM recipients 
WHERE  
orderDate > DateFrom AND orderDate < DateTo

Upvotes: 1

kneidels
kneidels

Reputation: 914

I landed up using the Format1 function, which seemed to solve the issue:

SELECT * FROM recipients 
WHERE  
orderDate > Format (#01/01/2017#,'dd/mm/yyyy')
AND orderDate < Format (#10/02/2017#,'dd/mm/yyyy')

Though I still dont understand fully what was wrong with my initial query. I guess a date format issue: dd/mm or mm/dd

Thanks

Upvotes: 0

Related Questions