I'm having a problem with my SQL. I'm trying to create a keyword search that searches from 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 and the instrument it is to be played on, 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

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.

Thanks.

Try

www.dmxzone.com/showDetail.asp?TypeId=2&NewsId=353

or search DMX Zone for 'advanced search'

DMX | ASP | VBScript | ACCESS | IIS5

Edited by - sesame on 06 Aug 2003 17:47:54