Want to Search Several Fields for the Same Keyword

Eyrehead · March 16th, 2007, 08:43 AM

I'm a graduate student who has used my limited knowledge of Access to construct a database to organize my notes. I've created three keyword fields that I use to code the contents of each record (1 record=1 note). I'd like to construct a query that will allow me to search all three fields for a particular keyword.

Could anyone fill me in on what the SQL would be for such a query? Again, I want a query that will yield a list of all records in which the keyword appears in any of the three fields.

echovue · March 16th, 2007, 08:48 AM

This would probably be easier to do in the query window, but the SQL would look something like this:

WHERE Field1 LIKE '*' * keyword & '*' OR Field2 LIKE '*' * keyword & '*' OR Field3 LIKE '*' * keyword & '*'

If you are calling the SQL from VBA, you may need to substitute the *'s for %'s

Hope that helps

Mike

Mike
EchoVue.com

Eyrehead · March 16th, 2007, 10:53 AM

Many thanks, Mike! I didn't think it was possible to do something like this in the query window. Could I trouble you to say a little more about how I'd set it up in the window (I always prefer to avoid SQL when I can)?

Again, real grateful to you.

Best,
E

echovue · March 16th, 2007, 11:08 AM

I'm having trouble thinking of how best to describe it, but here goes...

In access, go to the query tab, and then click "Create a new Query in design view"

THe screen you get will be divided in to, and there will be another window with all of tables listed.

Double click the tables you would like to add, and then click close when you are done.

If you need to set up joins, you can now do it by dragging one field to another, then right clicking and editing the properties to choose the type of join.

Double click any fields you want to return and they will appear down in the bottom section.

Down in the bottom section, you can now add criteria, just in the format

LIKE '*' & 'Keyword' & '*'

Put it on different lines for each field to make it an OR criteria.

Since I suspect that you may need to use SQL anyway, you can now select SQL View from the View menu, and will have the SQL nicely formatted.

There is also a way to have the query prompt you for the variable in the criteria, but I haven't done that for a long long time.

Typically I use this to build my SQL strings, and then transfer them to my code - Heck, I even use it when I need to build SQL for PHP/MySQL apps!

Yell if you need more info,

Mike

Mike
EchoVue.com