Filter Form Data using Dates

[Ksabai]

Iam using the below code for filtering a data in a Subform, the filter works for dates in the current year. but if i include the from date starting 2018. it shows all the datas. kindly help.

If Len(txtFromDate) > 0 And Len(txtTodate) > 0 Then
If filterStr = "" Then
filterStr = filterStr & " ([ContDate] Between #" & txtFromDate & "# AND #" & txtTodate & "#)"

Else
filterStr = filterStr & " And ([ContDate] Between #" & txtFromDate & "# AND #" & txtTodate & "#)"
End If
ElseIf Len(txtFromDate) > 0 Then
If filterStr = "" Then
filterStr = filterStr & " ([ContDate] = #" & txtFromDate & "#)"
Else
filterStr = filterStr & " And ([ContDate] = #" & CDate(txtFromDate) & "#)"
End If
ElseIf Len(txtTodate) > 0 Then
If filterStr = "" Then
filterStr = filterStr & " ([ContDate] = #" & txtTodate & "#)"
Else
filterStr = filterStr & " And ([ContDate] = #" & CDate(txtTodate) & "#)"
End If
End If
Me.frmContList.Form.Filter = filterStr
If filterStr <> "" Then
Me.frmContList.Form.FilterOn = True
Else
Me.frmContList.Form.FilterOn = False
End If

 

[June7]

So it works if date criteria are for 2019?

Are you using DD/MM/YYYY structure for date criteria?

Have you step debugged code?

Please use CODE tags around code to retain indentation and readability.

 

[MajP]

I would modify the code to wrap all the dates. If not in US regional settings you will have to.

Dim strFrom As String
Dim strTo As String

If IsDate(txtFromDate) And IsDate(txttoDate) Then
    strFrom = Format(Me.txtFromDate, "MM/DD/YYYY")
    strFrom = "#" & strFrom & "#"
    strTo = Format(Me.txttoDate, "MM/DD/YYYY")
    strTo = "#" & strTo & "#"
        If filterStr = "" Then
          filterStr = filterStr & " ([ContDate] Between " & strFrom & " AND " & strTo & ")"
        Else
          filterStr = filterStr & " And ([ContDate] Between " & strFrom & " AND " & strTo & ")"
        End If
    ElseIf Not IsDate(txttoDate) Then
       strFrom = Format(Me.txtFromDate, "MM/DD/YYYY")
       strFrom = "#" & strFrom & "#"

        If filterStr = "" Then
          filterStr = filterStr & " ([ContDate] = " & strFrom & ")"
        Else
          filterStr = filterStr & " And ([ContDate] = " & strFrom & ")"
        End If
    ElseIf Not IsDate(txtFromDate) Then
        strFrom = Format(Me.txttoDate, "MM/DD/YYYY")
        strFrom = "#" & strTo & "#"
        If filterStr = "" Then
          filterStr = filterStr & " ([ContDate] = " & strTo & ")"
        Else
          filterStr = filterStr & " And ([ContDate] = " & strTo & ")"
        End If
    End If
    Me.frmContList.Form.Filter = filterStr
    If filterStr <> "" Then
      Me.frmContList.Form.FilterOn = True
    Else
      Me.frmContList.Form.FilterOn = False
    End If
End If