I'm having a problem with my SQL. I'm trying to create a keyword search that searches more than one field in the same table of my Microsoft Access 2000 database. What I want to do is allow people to put more than one word in the search box and have results from several fields appear. I can get it so that it searches using one keyword but not 2. i.e. my page is to search a database of bowed instrument strings and I want people to be able to search for, both the type of string(Description) and the instrument it is to be played on(FurtherCategory), not one or the other.

My SQL looks like this at the moment (I know its wrong but it does search one or the other keywords just not both together):

SELECT ID, Number, CatalogueNo, InstrumentType, FurtherCategory, Thumbnail, Description, FurtherDescription, Details, Weak, Medium, Strong, Information, ExtraComments
FROM ThomastikStrings
WHERE InstrumentType= 'Bowed Instrument String' AND ( Description LIKE '%txtSearchField%' AND FurtherCategory LIKE '%txtSearchField%' OR Description LIKE '%txtSearchField%' OR FurtherCategory LIKE '%txtSearchField%' )
ORDER BY Number

txtSearchField is the name of the variable for searchfield which is the text box in the form.
Please can someone help? all the information I need is in one table and I've selected nearly all the fields as I have the results appear on the same page if the search is succesful.
I'm doing all this in the recordset box in Ultradev4.

Thanks.

Kirsty

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I'm having a problem with my SQL. I'm trying to create a keyword search that searches more than one field in the same table of my Microsoft Access 2000 database. What I want to do is allow people to put more than one word in the search box and have results from several fields appear.

My SQL looks like this:

SELECT ID, Number, CatalogueNo, InstrumentType, FurtherCategory, Thumbnail, Description, FurtherDescription, Details, Weak, Medium, Strong, Information, ExtraComments
FROM ThomastikStrings
WHERE InstrumentType= 'Bowed Instrument String' AND ( Description LIKE '%txtSearchField%' AND FurtherCategory LIKE '%txtSearchField%' OR Description LIKE '%txtSearchField%' OR FurtherCategory LIKE '%txtSearchField%' )

ORDER BY Number<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

HI there,

I posted another topic that is exactly like yours, and in the meantime I searched for an answer, since my project needs to go live NOW.

Well, I did find an answer just about an hour ago, and it works great. The answer is amazingly simple too:

Make your SQL like this:

SELECT * FROM TABLE WHERE (FIELD_1 LIKE '%search_string%' OR FIELD_2 LIKE '%search_string%')

This will search both FIELD_1 and FIELD_2 for whatever the search string is. You could expand the SQL statement to include as many ORs as you needed.

That's all there is to it!

I hope this helps you,
Mike