Query searching multiple fields

[Mozy]

I'm a complete novice so bear with me. I have been asked to set up an
enquiries database that includes an Enquiry field, Keyword1, Keyword2,
Keyword3 and Keyowrd4 fields (among others). I would have preferred one
keyword field with free text but the boss, you know ...

How do I set up a query across all those 5 fields. e.g. if I wanted to
search for organic fruit but 'organic' is in one field and 'fruit' is in
another? I've been able to set up a search for a single word but not linking
multiple words.

Any help most gratefully received!

 

[Allen Browne]

You will need some experience with VBA coding to achieve this.

In essence, you will need to create a filter string that uses Split() to
parse the words from the text box where the user entered their search words,
and then build a Filter string that will end up like this:
strFilter = "Field1 Like ""*organic*"" OR Field1 Like ""*fruit*"" OR
Field1 Like ""*organic*"" OR ...

Part of the problem here is that you have a non-normalized design. Whenever
you see repeating fields in your table (like Keyword1, Keyword2, ...), it
always means you need a related table where there can be many *records*
related to this one, instead of many columns in this one. If you did that,
you could do it with subqueries or possibly even with a Totals query.

The new table would have fields like this:
- EnquiryID relates to the primary key of your main table.
- Keyword Text the keyword to associate with this record.
For the interface, you probably already have a form for entering the
enquiries, so you could add a subform for the keywords (continuous view, one
per row, as many as you need.)

Now there's only one field to search to find any particular keyword.