Query searching multiple fields   

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!

date: Tue, 30 Sep 2008 05:52:02 -0700   author:   Mozy

Re: Query searching multiple fields   

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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mozy"  wrote in message
news:A65412CF-3F4D-46FC-9371-B3BDD9E08466@microsoft.com...
> 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!

date: Tue, 30 Sep 2008 21:18:57 +0800   author:   Allen Browne lid