Searching a field for:exact match/any of words contained in/any words in any order?

I have a simple search running but I've found it to be pretty useless so far.

I'm not overly sure how search engines operate (ie. in their basic search, they seem to find your search string in records/fields where 2 or more words match), but maybe someone can enlighten me how to do this ?

I am trying to search a string for matches in one particular field, Can anyone help me alter my code so that it searches for:

-An exact match of search string to the field (Match exact phrase)

-A match where any of the words given are contained in the field in any order
(Match any word)

-A match where all of the words given are contained in the field (but maybe not having to be in the order they were given).

-A match where any of the words given are contained in field in similar order
(ie. If user entered "I am really confused" as the search string, it could pick out records that had strings such as "I am" or "really confused").

I know I may need a combo box for this, or cases, but can someone please guide me in the right direction as regards the queries?

My code so far is:


newMatchRS.Open "SELECT * From Task WHERE UCase(TaskInformation)
LIKE '%" & UCase(txtSearchCriteria) & "%' Order By TaskID", db,
adOpenStatic, adLockOptimistic


Thanks for any help

I would think using UNION queries, where multiple searches are done in the same SQL statement, would be a good solution. Each UNION query always returns the same number of fields and the same fields. If unfamiliar with UNION queries, you might want to use your database app's help or the tutorials on this site. The UNION query returns one recordset even though they can be consist of multiple queries.

you are making a UCASE() vs UCASE() comparison. The LIKE keyword will return any similar words to the one in question. I won't retun a group or sentence.

It seems to be returning a string if the string is *exactly* the same.
If the whole string is not the same, it will return it up to the point where the match ends (eg. even if it only ends after the first word).

Its still not a great search though - could do with better

END LIKE ENDLESS....this should work

END LIKE ENDS ...this should work

END LIKE MKOSLOF IS WRITING THE WORD TENDENCY ...won't work.

You need to understand the string as a whole is being evaluated. The LIKE method is not going to return true for what you need.