How do I search multiple fields in a table using one search box?

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

Guest

I have a contact table with multiple fields (ie, Name, address, city,
phone)...I would like to be able to type data in one search field and have
the search look in each of the above mentioned fields for the data and then
return the possible matches in a continuous form or list box. This would
work similar to Outlook when you use the Find feature on the Contact listing.
 
mmarks said:
I have a contact table with multiple fields (ie, Name, address, city,
phone)...I would like to be able to type data in one search field and have
the search look in each of the above mentioned fields for the data and then
return the possible matches in a continuous form or list box. This would
work similar to Outlook when you use the Find feature on the Contact listing.
Click to expand...


Base the continuous form on a query that uses the search
control as its criteria for each of the fields. If you want
to only find exact matches, the criteria would be of this
form:
Forms![name of form].[name of control]
If you want to find partial matches, the criteria would be:
Like "*" & Forms![name of form].[name of control] & "*"

The key is to put the criteria on different Criteria rows so
that matching any field is sufficient. (If you put the
criteria all on the same Criteria row, then would all have
to match.)
 
Marsh, thanks for the input...you helped me fix the problem:)...I had
neglected to put each criteria, Like (nz("*" &
[forms]![frmFindCustomer]![txtSearch] & "*","*")), on a different row in the
query.
--
mmarks


Marshall Barton said:
mmarks said:
I have a contact table with multiple fields (ie, Name, address, city,
phone)...I would like to be able to type data in one search field and have
the search look in each of the above mentioned fields for the data and then
return the possible matches in a continuous form or list box. This would
work similar to Outlook when you use the Find feature on the Contact listing.
Click to expand...


Base the continuous form on a query that uses the search
control as its criteria for each of the fields. If you want
to only find exact matches, the criteria would be of this
form:
Forms![name of form].[name of control]
If you want to find partial matches, the criteria would be:
Like "*" & Forms![name of form].[name of control] & "*"

The key is to put the criteria on different Criteria rows so
that matching any field is sufficient. (If you put the
criteria all on the same Criteria row, then would all have
to match.)
Click to expand...
 
mmarks said:
Marsh, thanks for the input...you helped me fix the problem:)...I had
neglected to put each criteria, Like (nz("*" &
[forms]![frmFindCustomer]![txtSearch] & "*","*")), on a different row in the
query.
Click to expand...


It is useful (not to mention educational) to switch a query
to SQL view so you can see what's really goin on. In this
case it should have been immediately obvious that the
criteria had AND between them when you need them to have OR.

Note that your use of Nz is useless. After concatenating
the *s, the pattern can never be Null. While what you have
works, it will confuse anyone looking at it. They will be
wondering what subtle trick they are missing, when it is
just an unnecessary use of a function.
 
Back
Top