multi-word search

[gabrielsoy]

In a query I have a field where I concatenate the contents of other text fields. I prompt the user for a Search string (can be one, or several words), and I want to do a search in that concatenated field, so that I find records with the most words from the user's input.

Example:
I have the following fields:
1-"Space bar try our special margaritas, wine vodka, cigars"
2-"Bar Don Pepe. Wine, vino, cigars, cognac"
3-"Our bar. Cocktails. margaritas, wine"

If the user enters "wine, Pepe", he will fist get field 2 (both wine and Pepe are there), then 1 and then 3 (bar).

If the user enters "bar, cigars" he will get fields 1 & 3, then 2 (bar).

I have though if the user input is only one word, I could use it as criterium and see which fields contain it. But I dont know what to do if the user enters two words, or three, or ten...

Anybody can help?

Thanks, a million.

 

[ajetrumpet]

You cannot do this (not that I know of anyway). If you even have a chance, you'll have to do it through VBA, but even then, it will be way too difficult I think to even try it.

 

[dkinley]

As stated - it would be very complex.

Do searches on parsing strings. There may be something out there someone already has done. Once you have the words parsed, you would then need some nifty logic to record hits and misses and keep track of what hit and what missed. That is the first part.

The second part is the decision making that would need to occur to decide which fields to show or not show.

You could do individual field searches (anding and orring the criteria)to limit the returned records which you could then concantenate and present to the user.

-dK

 

[georgedwilkinson]

More info:
If you build a where clause using the parsed string, you need to be cautious of database performance. Unless you do full text indexing, your database will slow to a crawl if your data set is large and your criteria are complex.

Here's a nice article on the difficulties: http://www.dbms2.com/2005/12/09/relational-dbms-versus-text-data/

 

[raskew]

Hi -

See if this would be of assistance: http://www.access-programmers.co.uk/forums/showthread.php?t=154683

HTH - Bob