Multiple text filters in Microsoft Access 2010

Question

I would like to filter through my data with two text boxes, StaffTotalSearchText1 and StaffTotalSearchText2.

I understand that only the most recent DoCmd.ApplyFilter command appies, and that means I don't know how to apply two text filters.

Edit: Solution found - see Johnny's RecordSource code One way of applying a filter is to do it do the loaded data (RecordSource) - still allowing a "DoCmd.ApplyFilter" to be used.

I've created a "lock filter 1" button. When enabled, the filter applies; when disabled, there is no filter.

Johhny's answer has the correct code to filter the loaded data upon loading it.

Answer 1

Just use both values in filter and the same code in both filter events:

DoCmd.ApplyFilter "", _
 "[Forename] Like '*" & me![StaffTotalSearchText1] & "*'" & _
 " Or [Surname] Like '*" & me![StaffTotalSearchText1] & "*'" & _
 " Or [Forename] Like '*" & me[StaffTotalSearchText2] & "*'" & _
 " Or [Surname] Like '*" & me[StaffTotalSearchText2] & "*'"

Edit: applying the second filter after the first:

If Not IsNull(Me![StaffTotalSearchText1]) And Len(Me![StaffTotalSearchText1] & "") <> "" Then
    strFilter = "[Forename] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
              " Or [Surname] Like '*" & Me![StaffTotalSearchText1] & "*'"
    If Not IsNull(Me![StaffTotalSearchText2]) And Len(Me![StaffTotalSearchText2] & "") <> "" Then
        strFilter = "(" & strFilter & ") AND " & _
                  "( [Forename] Like '*" & Me![StaffTotalSearchText2] & "*'" & _
                  " Or [Surname] Like '*" & Me![StaffTotalSearchText2] & "*')"
    End If
End If

DoCmd.ApplyFilter "", strFilter

If you want to use RecordSource you can use the same filter but must add the SQL for select command:

strFilter = ""

If Not IsNull(Me![StaffTotalSearchText1]) And Len(Me![StaffTotalSearchText1] & "") <> "" Then
    strFilter = "[Forename] Like '*" & Me![StaffTotalSearchText1] & "*'" & _
              " Or [Surname] Like '*" & Me![StaffTotalSearchText1] & "*'"
    If Not IsNull(Me![StaffTotalSearchText2]) And Len(Me![StaffTotalSearchText2] & "") <> "" Then
        strFilter = "(" & strFilter & ") AND " & _
                  "( [Forename] Like '*" & Me![StaffTotalSearchText2] & "*'" & _
                  " Or [Surname] Like '*" & Me![StaffTotalSearchText2] & "*')"
    End If
End If

If strFilter <> "" Then
    strFilter = "SELECT * FROM StaffTable WHERE " & strFilter
Else
    strFilter = "[StaffTable]"
End If

Me.RecordSource = strFilter

Answer 2

Try this:

Me.RecordSource = "SELECT * FROM StaffTable " & _
"WHERE [Forename] Like '*" & [Forms]![StaffTotalQuery]![StaffTotalSearchText1] & "*' " & _
   "Or [Surname] Like '*" & me![StaffTotalSearchText1] & "*' " & _
   "Or [ProfessionalID] Like '*" & me![StaffTotalSearchText1] & "*'"

Assuming you're pointing to the correct objects (I'm guessing Forename is on a different form? It's the only one you're referencing by form name), this should work.