filter based on range of date field not working properly

[siamandm]

Hello All,

I'm using this code to filter by date, but it's not showing the result as expected.
for example,
if I have a series of date like below :
6/6/2021
9/6/2021
25/6/2021
1/4/2021



and when I use this code below to do the filter it do the filter but not as expected ...
if I put 1/5/2021 inside txtStartDate and 29/5/2021 inside txtEndDate , the result I get is 1/4/2021 which I should not get any result

Code: Select all

 If Not IsNull(Me.txtStartDate) Then
                 strWhere = strWhere & " AND WorkingDate>=#" & Format(Me.txtStartDate, "dd/mm/yyyy") & "#"
                 End If
                 
                 If Not IsNull(Me.txtEndDate) Then
                 strWhere = strWhere & " AND WorkingDate<=#" & Format(Me.txtEndDate, "dd/mm/yyyy") & "#"
                 End If

[HansV]

A where-condition uses SQL syntax, and SQL expects dates in either US date format mm/dd/yyyy or in ISO date format yyyy-mm-dd, not in dd/mm/yyyy format. So use

Format(Me.txtStartDate, "mm/dd/yyyy")

and

Format(Me.txtEndDate, "mm/dd/yyyy")

[siamandm]

Saying thank you is not enough
God return all this helps with happiness and good health