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

0
votes

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.

1
sqlms-access
What does the original data look like? - Robert Harvey
If TransactionDate's data type is Date/Time remove the function Format and compare TransactionDate. - forpas
@forpas Seems something was messed up with this Format function, after removing that format, its working fine. - Mox Shah
Format a date, and it's no longer a date, it is a string. BETWEEN AND expects dates. - Robert Harvey
Do note that if TransactionDate contains a time part, then #11-Feb-2020# will be interpreted as midnight so it will ignore the rest of the day (solution: use < #12-Feb-2020# or rather < #2020-02-12#) - Hans Kesting

1 Answers

1
votes

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#