Filter between dates in subform (1 Viewer)

theinviter

Registered User.
Local time
Today, 08:58
Joined
Aug 14, 2014
Messages
237
Hi
I have a Subfor " Table2" as it have 2 date filter field [ Date_From] and [Date_To].
the subform has Date filed [Date_]. so i want to filter the form between the 2 dates after updates.
how to modify this code to apply it filter date.

Code:
Private Sub filterThisForm2()
Dim S1, S2
    On Error GoTo errhandler
    'n = 1 / 0 ' cause an error
    S1 = ""
 
 
     If Len(Me!Date_From & "") <> 0 Then
        S1 = S1 & " and [Date_] Like  '" & Me!Date_From & "'  "
    End If
 
    If Len(S1) > 5 Then
        S1 = Mid(S1, 5)
        With Me.Form
            .Filter = S1
            .FilterOn = True
        End With
    Else
        Me.Form.FilterOn = False
    End If

    Exit Sub
errhandler:
    ' error handling code
    Resume Next
End Sub
 
Last edited by a moderator:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:58
Joined
Feb 19, 2013
Messages
16,553
please use code tags (select the code and click the </> button) to preserve indentation of your code to make it more readable - I've done if for you on this occasion.

Don't have time to consider your question as I'm about to go out but don't quite understand the question. You say

I have a Subfor " Table2" as it have 2 date filter field [ Date_From] and [Date_To].

and

the subform has Date filed [Date_].

so all the controls are on the same form?
 

cheekybuddha

AWF VIP
Local time
Today, 15:58
Joined
Jul 21, 2014
Messages
2,237
I think you will need something more along the lines of:
Code:
Private Sub filterThisForm2()
On Error GoTo errhandler

  Dim strFilter As String

  If IsDate(Me.Date_From) Then
    strFilter = "[Date_] >= " & Format(Me.Date_From, "yyyy-mm-dd hh:nn:ss")
  End If
  If IsDate(Me.Date_To) Then
    strFilter = IIf(Len(strFilter) > 0, " AND ", vbNullString) & _
                "[Date_] <= " & Format(Me.Date_To, "yyyy-mm-dd hh:nn:ss")
  End If
 
  With Me.NameOfSubformControlWithFormTable2AsItsSourceObject.Form
    .Filter = strFilter
    .FilterOn = Len(strFilter) > 0
  End With

  Exit Sub

errhandler:
  ' error handling code
  Resume Next

End Sub

This assumes that [Date_From] and [Date_To] are controls on the main form.

You will need to substitute "NameOfSubformControlWithFormTable2AsItsSourceObject" with the actual subform control name. Note this may or may not be different from "Table2" or whatever is used as its SourceObject.
 

Users who are viewing this thread

Top Bottom