Please try this (make a backup copy of your file first), note that you need to add a couple functions and some lines at the top of the form module:
Code:
Option Compare Database
Option Explicit
Public Enum CombineFilterType
ct_And = 0
ct_OR = 1
End Enum
Private Sub cmdFilterAll_Click()
Dim sFilter As String
sFilter = GetFilter(ct_And)
If sFilter = "" Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = sFilter
Me.FilterOn = True
End If
End Sub
Public Function GetFilter(AndOr As CombineFilterType) As String
'need variable for each partial filter
Dim strRefNumFltr As String
Dim strDate_SentFltr As String
Dim strItemFltr As String
Dim strUpdatedByFltr As String
'ref number
If (Me.txtRefFilter & "") <> "" Then
strRefNumFltr = "[Ref Num] Like ""*" & Me.txtRefFilter & "*"""
End If
'Date Sent
If Not IsNull(Me.txtStartFilter) And Not IsNull(Me.txtEndFilter) Then
strDateFltr = "[Date Sent] Between #" & Me.txtStartFilter & "# AND #" & Me.txtEndFilter & "#"
End If
' if only start date is entered
If Not IsNull(Me.txtStartFilter) And IsNull(Me.txtEndFilter) Then
strDateFltr = "[Date Sent] >= #" & Me.txtStartFilter & "#"
End If
' if only end date is entered
If Not IsNull(Me.txtEndFilter) And IsNull(Me.txtStartFilter) Then
strDateFltr = "[Date Sent] Like <= #" & Me.txtEndFilter & "#"
End If
'UpdatedBy
If Not IsNull(Me.txtUpdatedByFilter) Then
strUpdatedByFltr = "[Updated By] Like ""*" & Me.txtUpdatedByFilter & "*"""
End If
'Item
If Not IsNull(Me.txtItemFilter) Then
strItemFltr = "([Item1] Like ""*" & Me.txtItemFilter & _
"*"" or [Item2] Like ""*" & Me.txtItemFilter & "*"" or [Item3] Like ""*" & _
Me.txtItemFilter & "*"" or [Item4] Like ""*" & Me.txtItemFilter & _
"*"" or [Item5] Like ""*" & Me.txtItemFilter & "*"" or [Item6] Like ""*" & _
Me.txtItemFilter & "*"" or [Item7] Like ""*" & Me.txtItemFilter & _
"*"" or [Item8] Like ""*" & Me.txtItemFilter & "*"" or [Item9] Like ""*" & _
Me.txtItemFilter & "*"" or [Item10] Like ""*" & Me.txtItemFilter & _
"*"" or [Item11] Like ""*" & Me.txtItemFilter & "*"" or [Item12] Like ""*" & _
Me.txtItemFilter & "*"" or [Item13] Like ""*" & Me.txtItemFilter & _
"*"" or [Item14] Like ""*" & Me.txtItemFilter & "*"" or [Item15] Like ""*" & _
Me.txtItemFilter & "*"")"
End If
GetFilter = CombineFilters(AndOr, strRefNumFltr, strDateFltr, strUpdatedByFltr, strItemFltr)
End Function
Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
Dim FilterCombiner As String
Dim i As Integer
Dim strOut As String
If And_Or = ct_And Then
FilterCombiner = " AND "
Else
FilterCombiner = " OR "
End If
For i = 0 To UBound(Filters)
If Filters(i) <> "" Then
If strOut = "" Then
strOut = Filters(i)
Else
strOut = strOut & FilterCombiner & Filters(i)
End If
End If
Next i
CombineFilters = strOut
End Function
Cheers,