Access Memo Field Search

[CoreW50]

Hello, I am trying to develop something, but I am running into some trouble.
I need to do a search on a memo field based on a long string. Basically, I
would like it to work like a google search where I type in something like,
Rules for large composting facilities, and it returns the results ranked in
relevance order. I am thinking that this may be impossible to do, but I
thought I may ask here for some help.

I currently have it set up where the user types into a text box and all the
words in the text box are put in their own text box, and a query is run for
each of those boxes and the results are ordered based on which records had
the words that have been broken out into their own text boxes. It doesn't
seem to return good results.

I know about using *Forms!frm_Search!Word1*, but sometimes the results don't
have all the words included and I would still like to have them displayed.

Has anyone ever seen this type of search done?

 

[Dale_Fye via AccessMonster.com]

Not sure this is exactly what you are looking for, but it will get you close.
When I want to institute a text search that includes multiple words, I
generally create a keyword table, that contains the parsed values from the
text string I entered (after deleting the words from the previous search).

I then create a query (something like below) that identifies the records that
contain the words indicated:

SELECT A.ID,
(LEN(A.MemoField) - fnReplace(A.MemoField, B.KeyWord, ""))/ LEN(B.
KeyWord) as Freq
FROM A, B
WHERE instr(A.MemoField, B.KeyWord) > 0

This uses fnReplace( ) which is just a wrapper to the VBA Replace( ) function
to replace each occurance of the keyword with an empty string. By doing this,
and comparing the length of the original MemoField to the version without the
keyword, and then dividing by the length of the keyword, you can determine
the number of occurances of each keyword in the MemoField. If you have
really long MemoFields or have lots of key words, this can take a while. If
all you are concerned with is the occurance of a single instance of the
keyword, you could replace the Freq portion of the above with

Instr(A.Memofield, B.KeyWord) as FirstInstance

You can then use another query to determine how many of the key words you
entered were matched, and develop your "relevance" factor.

HTH
Dale