Form filter which searches multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Jennifer,

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
Else
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.

HTH,
Nikos
 
Back
Top