Search function

[SwEdIsH_OfFiCe_UsEr]

I have a form with several search boxes which all are connected to one field
each but I want a search box that searches more than one field and returns
all that match. I.e., if I have entered "house" in my title box and "holiday"
in my search-all box I want every post with "house" in the title and every
post with "holiday" in desired fields even if they don't have "house" in the
title. I tried this code, but it didn't work:

If Not IsNull(Me.SearchTitleBox) Then
strWhere = strWhere & " and " & "[table.title] Like '*" &
Me.SearchTitleBox & "*'"
End If
If Not IsNull(Me.SearchAllBox) Then
strWhere = strWhere & " and " & "[table.title] Like '*" &
Me.SearchAllBox & "*'" & " or " & "[table.category] Like '*" &
Me.SearchAllBox & "*'"
End If

To what do I change the code to do what I want?

 

[Douglas J. Steele]

The use of And and Or in boolean expressions is usually a little different
than the use of those phrases in English.

strWhere is going to end up being something like:

and [table.title] Like '*house*' and [table.title] Like '*holiday*' or
[table.category] Like '*holiday*'

And takes precedence over or, so that probably will never find anything
(unless you've got a title that has both house and holiday in it)

You need something like

and

.[title] Like '*house*' or (

.[title] Like '*holiday*' or

.[category] Like '*holiday*')

(note the change to the square brackets in addition to the change from and
to or and additional parentheses)