Please excuse this question, but I'm new to SQL queries.

I want to search on a field, it's a varchar field.
I'm using:
SELECT * FROM table1 WHERE field LIKE "searchterm"

My problem - If I use "lead based paint" for my search term, and I know I have 10 records that exist, how can I get them all to show if I enter terms such as:

lead paint
lead-based paint
etc?

Should this field be indexed?

TIA for any help.

If you whant it to be more un precise this is what you do

SELECT * FROM produkts WHERE produktspecs LIKE "%searchterm%"

if you type lead
it will find lead, leadpaint, paintlead, everything that holds the char "lead"

if you only have "searchterm%"
it will find the ones that start with lead but not the ones that endy with lead...

// Leon

Thanks for the reply, but I'm already using
%searchterm%. The problem is that this is a field from a search form. The entry in the database is "lead-based paint". If I enter "lead", I get results. With "paint" I get results. With "lead paint", there's no results. Any ideas?

Instead of LIKE try using CONTAINS

Cheers

// Leon

Try Breaking up separate words entered in the Search text field. For example if the user enters - lead based paint - the search will look for matches with lead and based and paint.

Take a look at the link at the bottom of the page for a tutorial on how to build multiple option searches.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo