Filter between dates in subform

[theinviter]

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.

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

 

[CJ_London]

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]

I think you will need something more along the lines of:

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.