VBA Filter Contious Form - Multple Textboxes

Question

I am trying to create a filter that uses 3 textboxes (tbLastNameFilter, tbFirstNameFilter, and tbCompanyFilter. I have successfully managed to get it to work with one. However, I am not sure how I would go about getting it to work in unison with the other two. I have tried couple of ways.

Private Sub bttnSearch_Click()
Dim strFilter As String

If IsNull(Me.tbLastNameFilter & Me.tbFirstNameFilter & Me.tbCompanyFilter) Then
 MsgBox ("No Search Information Entered")
 Me.FilterOn = False
Else
 strFilter = "LastName Like '*" & Replace(Me.tbLastNameFilter, "'", "''") & "*'"
 Me.Filter = strFilter
 Me.FilterOn = True
End If

I have tried changing the strFilter to

strFilter = "LastName Like '*" & Replace(Me.tbLastNameFilter, "'", "''") & "*'" & _
"FirstName Like '*" & Replace(Me.tbFirstNameFilter, "'", "''") & "*'" & _
"Company Like '*" & Replace(Me.tbCompanyFilter, "'", "''") & "*'"

If I leave any one of the boxes blank I get an invalid use of null and if I put letter into each I get Syntax error (missing operator).

I would like to be able to enter anything into one or all of the boxes, click search and see matched criteria.

Answer 1

You are missing the AND in the query. But you also need to check if the textbox is empty before adding it to the filter. I suggest to do this with two subs.

The first one addToFilter manipulates the control and adds it to the filter if it is not empty, and it adds the AND only if necessary. This approach simplifies the code as it factorizes some of the common string manipulations.

Sub addToFilter(ByRef sFilter As String, ctrl As Object, fieldName As String)
    If IsNull(ctrl.Value) Then Exit Sub
    If Len(Trim(ctrl.Value)) = 0 Then Exit Sub
    If Len(sFilter) <> 0 Then sFilter = sFilter & " AND "
    sFilter = sFilter & fieldName & " Like '*" & Replace(Trim(ctrl.Value), "'", "''") & "*'"
End Sub

Private Sub bttnSearch_Click()
    Dim strFilter As String
    addToFilter strFilter, Me.tbLastNameFilter, "LastName"
    addToFilter strFilter, Me.tbFirstNameFilter, "FirstName"
    addToFilter strFilter, Me.tbCompanyFilter, "Company"

    If Len(strFilter) = 0 Then
        MsgBox ("No Search Information Entered")
        Me.FilterOn = False
    Else
        Me.filter = strFilter
        Me.FilterOn = True
    End If
End Sub