CODEWITHSUNDEEP
sqlms-access
Mox Shah
Mox Shah

Reputation: 3015

Between operator for date comparison not working properly in MS Access DB query

I have simple query as 

Select *
from myTable tran

Where  tran.Party = 13  
AND  
Format(TransactionDate,'dd-mmm-yyyy') BETWEEN #07-Jan-2020# AND #11-Feb-2020#

Which returns just one record where transaction date is "07-Jan-2020" and not other records which are falling between these dates.

Update

enter image description here

Update 2:

Instead of between operator if I use 'Greater Than' && 'Less Than' operator:

Select * from CylinderTransactions tran

Where  tran.Party = 13  AND
Format(TransactionDate,'dd-mm-yyyy') >= #07-Jan-2020# 
AND Format(TransactionDate,'dd-mm-yyyy') <= #11-Feb-2020#

Than it gets 3 records one from 7th Jan and 2 from 11th Feb, but still one record of 15th Jan (Please refer previous snapshot of actual data.

Upvotes: 0

Views: 183

Answers (1)

Gustav
Gustav

Reputation: 56016

Filter on the date value itself and never use literal months:

Where  
    tran.Party = 13  
    AND
    TransactionDate BETWEEN #2020/01/07# AND #2020/02/11#

Upvotes: 1

Related Questions