Solved Filter Between Dates (1 Viewer)

[theinviter]

Dears;
I have a sub form that contain Date Filter filed ( Date_From and Date_To). so i tried to filter the form with below code and work fine.
but i clear the filter by clicking on button, got error message " The Search key was not found in any record"
so how to avoid this error.

Private Sub filterThisForm2()
Dim Filter As String

If IsNull(Me.Date_From) Or IsNull(Me.Date_To) Then
Me.FilterOn = False
Else
Filter = "[Date_] BETWEEN #" & Format(Me.Date_From, "yyyy\/mm\/dd") & "# AND #" & Format(Me.Date_To, "yyyy\/mm\/dd") & "#"
' Print the filter string to the Immediate window (press Ctrl+G).
Debug.Print Filter
Me.Filter = Filter
Me.FilterOn = True
End If


End Sub





Private Sub Clear_Click()
Date_From = ""

Date_To = ""

Call filterThisForm2
End Sub

 

[CJ_London]

Just use

me.filteron=false

 

[theinviter]

Just use

me.filteron=false

what if i dont want to filter in between, as i want to filter after update each filed separately. Mean i fuser updated "Date_From" then filter the form. same things to do with "Date_To"

 

[CJ_London]

for from

Filter = "[Date_] >= #" & Format(Me.Date_From, "yyyy\/mm\/dd") & "#"

for to
Filter = "[Date_] <= #" & Format(Me.Date_To, "yyyy\/mm\/dd") & "#"

 

[arnelgp]

Private Sub filterThisForm2()
Dim Filter As String

If IsDate(Me.Date_From)=False And  IsDate(Me.Date_To)=False Then
Me.FilterOn = False
Else
Filter = "[Date_] >= #" & Format(Nz(Me.Date_From, 1), "yyyy\/mm\/dd") & "# AND [Date_] <= #" & Format(Nz(Me.Date_To,2958465), "yyyy\/mm\/dd") & "#"
' Print the filter string to the Immediate window (press Ctrl+G).
Debug.Print Filter
Me.Filter = Filter
Me.FilterOn = True
End If
End Sub





Private Sub Clear_Click()
Date_From = Null
Date_To = Null
Call filterThisForm2
End Sub

 

[theinviter]

Private Sub filterThisForm2()
Dim Filter As String

If IsDate(Me.Date_From)=False And  IsDate(Me.Date_To)=False Then
Me.FilterOn = False
Else
Filter = "[Date_] >= #" & Format(Nz(Me.Date_From, 1), "yyyy\/mm\/dd") & "# AND [Date_] <= #" & Format(Nz(Me.Date_To,2958465), "yyyy\/mm\/dd") & "#"
' Print the filter string to the Immediate window (press Ctrl+G).
Debug.Print Filter
Me.Filter = Filter
Me.FilterOn = True
End If
End Sub





Private Sub Clear_Click()
Date_From = Null
Date_To = Null
Call filterThisForm2
End Sub

thanks alot that work,
but need to add a check list to filter along with date filter. how to add them to the code.

If IsNull(Me.Foc_Entery) Then
my_ck = " [Focus entry] like '*'"
Else
my_ck = " [Focus entry]= " & Me.Foc_Entery & ""
End If

If IsNull(Me.Floor_entry) Then
my_ck1 = " [Floor Stock] like '*'"
Else
my_ck1 = " [Floor Stock]= " & Me.Floor_entry & ""
End If

 

[arnelgp]

Private Sub filterThisForm2()
Dim Filter As String

If IsDate(Me.Date_From) = False And IsDate(Me.Date_To) = False And IsNull(Me.Floor_entry) And IsNull(Me.Floor_entry) Then
    Me.FilterOn = False
    Exit Sub
Else
    Filter = "[Date_] >= #" & Format(Nz(Me.Date_From, 1), "yyyy\/mm\/dd") & "# AND [Date_] <= #" & Format(Nz(Me.Date_To, 2958465), "yyyy\/mm\/dd") & "# And "
End If
If IsNull(Me.Foc_Entery) = False Then
    Filter = Filter & "[Focus entry]= " & Me.Foc_Entery & " And "
End If

If IsNull(Me.Floor_entry) = False Then
    Filter = Filter & "[Floor Stock]= " & Me.Floor_entry & " And "
End If
Filter = Left$(Filter, Len(Filter) - 5)
' Print the filter string to the Immediate window (press Ctrl+G).
Debug.Print Filter
Me.Filter = Filter
Me.FilterOn = True
End Sub

 

[theinviter]

Private Sub filterThisForm2()
Dim Filter As String

If IsDate(Me.Date_From) = False And IsDate(Me.Date_To) = False And IsNull(Me.Floor_entry) And IsNull(Me.Floor_entry) Then
    Me.FilterOn = False
    Exit Sub
Else
    Filter = "[Date_] >= #" & Format(Nz(Me.Date_From, 1), "yyyy\/mm\/dd") & "# AND [Date_] <= #" & Format(Nz(Me.Date_To, 2958465), "yyyy\/mm\/dd") & "# And "
End If
If IsNull(Me.Foc_Entery) = False Then
    Filter = Filter & "[Focus entry]= " & Me.Foc_Entery & " And "
End If

If IsNull(Me.Floor_entry) = False Then
    Filter = Filter & "[Floor Stock]= " & Me.Floor_entry & " And "
End If
Filter = Left$(Filter, Len(Filter) - 5)
' Print the filter string to the Immediate window (press Ctrl+G).
Debug.Print Filter
Me.Filter = Filter
Me.FilterOn = True
End Sub

its not filtering, please find the attached file, if you can take a look and help me.

thanks

 

[arnelgp]

check and test.

 

[theinviter]

check and test.

thanks alot:
I applied this code to filter the form after update, but it filter both form and subform.
how to modify it to filter form only.

Private Sub FindCode_AfterUpdate()

On Error GoTo errhandler

''n = 1 / 0 ' cause an error



Dim strFilter As String

strFilter = ""

If Len(Me.FindCode & "") <> 0 Then

strstrFilter = " or '' &

 Like '" & Me.FindCode & "'"

End If



If Len(Me.FindCode & "") <> 0 Then

strFilter = strFilter & " or '' & [Other Code] Like '" & Me.FindCode & "'"

End If





If Len(strFilter) > 0 Then

strFilter = Mid(strFilter, 4)

With Me.Form

Me.Filter = strFilter

Me.FilterOn = True
Me.Table1_Subform.SetFocus
DoCmd.GoToControl ("[Qnty_IN]")
DoCmd.GoToRecord , , acNewRec
End With

Else

Me.Filter = ""

Me.FilterOn = False

End If



Exit Sub

errhandler:

' error handling code

Resume Next
End Sub