Building a Search Engine (looking for individual words within fields)

[jwhearn]

I am building a search engine, and would like to get your ideas on how to (elegantly) search for individual (whole) words within fields. For instance if I want to search for a book by entering a word (or mulitiple words) into the Title field (of the search form), what is the best way to form the SQL string from these words. The way I've been doing it, is to use the Like Operator in the SQL string, but in order to search for individual (whole) words, I find that I have to use it four times for each word. This is what the WHERE clause looks like for one word (where TitleStr is the word):

"([Title] Like ""*[!a-z]" & TitleStr & "[!a-z]*"" Or [Title] Like """ & TitleStr & "[!a-z]*"" Or [Title] Like ""*[!a-z]" & TitleStr & """ Or [Title] Like """ & TitleStr & ""&quot"

All these cases come about because I have to account for the case where my search word is exactly equal to the title, the case where it is at the beginning of the title, at the end, and in the middle somewhere.

This works, even for multiple words accross multiple fields, but as you can imagine, the resulting SQL string can become pretty ugly.

Any ideas?
Thanks,
JW.

 

[gwinn7]

Yeah, what about using the "Instr" function? For further reference, check the VBA/Access Help Index.

Gary
gwinn7

 

[jwhearn]

I don't know why I hadn't considered that. The InStr function sounds great. For instance my WHERE clause could look like this:

&quot;InStr([Title], TitleStr) <> 0&quot;

Now all I need is to be able to narrow this down to whole words only. In other words, if TitleStr=&quot;age&quot;, then the query will return records with &quot;savage&quot;, &quot;forage&quot;, etc. in the title. Using [!a-z] does not seem to work with the InStr function. Or maybe it does, and I'm doing it wrong? Any thoughts on this?

Thanks,
JW.