Robust Search Function in Access-question again

[Petercheng]

[FONT=&quot]Hi All,
I have a question that was put forwarded a few year again by lilanngel in the forum.
I'm currently building a database to collect information on a bunch of books. Each book’s synopsis is entered as memo in the database. I want to add a search function so that people can find specific books by keying in word(s) that exist in the book synopsis field. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]For example, if book 1 has the synopsis like this “the backyard have many apple trees” and book 2 has the synopsis like this “the backyard have many apple and berry tree”. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]My question is: (1) How can I design search function that will find book(s) whose synopsis has word apple by keying in apple (not part of the word like: a, ap, app, appl, pple, etc).[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] (2) How can I design search function that will find book(s) whose synopsis has word apple and berry by keying in apple berry (not part of the words).[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]In answer the previous question, John Big Booty kindly recommended a program called Dynamic Multi Search with Memo Field search[/FONT]. It is useful to my first question, but not to my second question. That is , when you key in two words, it will return nothing.

[FONT=&quot]
Any ideas?

Thank you!![/FONT]

 

[jdraw]

Unless you separate the apple berry in your query 2, it would seem to be a string by default. That is, Access nor anything else would know you meant these as 2 strings separated by And.

You could have a rule that said -- if I type a string and there are spaces, select each string and substitute an " AND " for the space..

If you want full words then add a space at the start and end of your string.

You could also, depending on your needs, enter a string, and another string and select from a list whether there should be an AND or an OR between the strings to build the search criteria.