Searching multiple fields

[mhchap]

My database so far is one form that feeds data into one table of
several hundred records and I put a combo box with the wizard to
search for records by first name but I want the ability to search by
other fields like last name, company, city...etc. I'm not sure how to
do this with other combo boxes or text boxes and 1 search button?
Also, I want to display search results in the case for example if
searching by company then I want to display the company and all it's
subsidiaries.

 

[Beetle]

Allen Browne has a good example of this typr of search form at;

http://allenbrowne.com/ser-62.html

 

[mhchap]

Allen Browne has a good example of this typr of search form at;

http://allenbrowne.com/ser-62.html

Thanks.

I'm trying to figure my way around with the code below but when I
click the filter button on the form it brings up a window as if I had
clicked the filter button on the toolbar asking for Enter Parameter
Value, FirstName, then a text box, ok/cancel. I'm not sure what's
causing this.

Private Sub cmdFilterContacts_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterFirstName) Then
strWhere = strWhere & "([FirstName] = """ &
Me.txtFilterFirstName & """) AND "
End If

If Not IsNull(Me.txtFilterLastName) Then
strWhere = strWhere & "([LastName] = """ &
Me.txtFilterLastName & """) AND "
End If

'Another text field example for OrganizationName. Use Like to find
anywhere in the field.
If Not IsNull(Me.txtFilterOrganizationName) Then
strWhere = strWhere & "([OrganizationName] Like ""*" &
Me.txtFilterOrganizationName & "*"") AND "
End If

If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """)
AND "
End If

'Another etxt field for State
If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([State] = """ & Me.txtFilterState &
""") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'No there was nothing in the string so
show msg box.
MsgBox "Please enter at least one search criteria.",
vbCritical, "Error!"
Else 'Yes there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub