Form filter which searches multiple fields

Form filter which searches multiple fields

Post by SmVubmlmZX » Fri, 01 Jul 2005 03:56:06

I have the following code in Access 97 attached to On Click event in a form.

Private Sub Command55_Click()

Forms![ICB Customers].Form.FilterOn = True
Forms![ICB Customers].Filter = "[Company Name] Like ""*" & Me.Textbox1 & "*"""

End Sub

I would like to use one textbox on my form to enter different search
criteria. For example, search by company name or company number or state.
Can someone please help me build out this code to accomplish this?

Form filter which searches multiple fields

Post by Nikos Yann » Fri, 01 Jul 2005 17:56:57


Your code should look something like:

If Not IsNull(Me.Textbox1) Then
strFilter = "[Company Name] Like '*" & Me.Textbox1 & "*'"
End If
If Not IsNull(Me.Textbox2) Then
strFilter = strFilter & " AND [Company Number] =" & Me.Textbox2
End If
If Not IsNull(Me.Textbox3) Then
strFilter = strFilter & "[State] = '" & Me.Textbox3 & "'"
End If
If Not IsNull(strFilter) Then
If Left(strFilter, 5) = " AND " Then
strFilter = Right(strFilter, Len(strFilter) - 5)
End If
Forms![ICB Customers].Filter = strFilter
Forms![ICB Customers].FilterOn = True
Forms![ICB Customers].FilterOn = False
End If

Pls make sure you use the correct field and control names in the place
of my assumed ones. I have assumed Company Number is numeric, and that
for both Company Number and State you want an exact match, as opposed to
"contains string" on the name.