From date & to date is not Searching properly In MSHFlexgrid1

[shubhankarhaldar]

The problem is when I search for the data i.e people{Shubhankar Haldar} within specific date {[1/5/2020] & [18/5/2020]} as in attached picture, the output date which comes is not within specified date.Also some of the dates that falls under the the specified date are missing.

Private Sub search2_Click()
Set rs_pay = New ADODB.Recordset
Set adv_pay = New ADODB.Recordset
rs_pay.Open "Select sum(advalue) from (select ename,date1,advalue from advanceentry where date1>= # " & Format$(Me.DTPicker1.Value, "dd/MM/yyyy") & " # and date1<= # " & Format$(Me.DTPicker2.Value, "dd/MM/yyyy") & " # and ename='" & L_ename.Text & "')", cd_pay, adOpenStatic, adLockOptimistic


adv_pay.Open " select ename,date1,advalue,date2 from advanceentry where date1>= # " & Format$(Me.DTPicker1.Value, "dd/mm/yyyy") & " # and date1<= # " & Format$(Me.DTPicker2.Value, "dd/mm/yyyy") & " # and ename='" & L_ename.Text & "' order by date2", cd_pay, adOpenStatic, adLockOptimistic
If adv_pay.EOF Then
    MsgBox ("NO RCORDS FOUND FOR SELECTED DATE!")
    Else
Set MSHFlexGrid2.DataSource = adv_pay
If IsNull(rs_pay(0)) Then
    T_prday.Text = ""
    Else
    T_prday.Text = rs_pay(0)
    
    Me.cmd_print.Enabled = True
    End If
    End If
End Sub

[DataMiser]

Well first of all this has nothing to do with flexgrid. Whatever issue you have is with your query or your data.

Access expects mm/dd/yyyy for date formats you are trying to use dd/mm/yyyy which is likely the source of your issue.
http://allenbrowne.com/ser-36.html