Filter Form Data using Dates (1 Viewer)

Ksabai

Registered User.
Local time
Today, 11:33
Joined
Jul 31, 2017
Messages
104
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

AWF VIP
Local time
Today, 10:33
Joined
Mar 9, 2014
Messages
5,423
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

You've got your good things, and you've got mine.
Local time
Today, 14:33
Joined
May 21, 2018
Messages
8,463
I would modify the code to wrap all the dates. If not in US regional settings you will have to.
Code:
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
 

Users who are viewing this thread

Top Bottom