Create a Dynamic Search query

[susanmgarrett]

I have a table with a field containing song lyrics (Table=master,
field=lyrics). I need to provide a user interface so that my users can
search the lyrics field.

The problem is the users need to specify what type of search they're doing.
All of the below would be valid searches.
Like "*red*" and like "*white*" and like "*blue*"
Like "*red*" or like "*white*" or like "*blue*"
Like "*red*" or like "*white*" and like "*blue*"
Like "*red*" or like "*white*" and not like "*blue*"

I have been told that I need to create a dynamic sql runtime, as the
parameter query is limited and the QBF won't allow for all query options.
This is equivalent to tell ingme I have to create a lunar landing module - I
know some of the theory, but having it turn out well is doubtful because I
have no idea where to begin or all of the details involved

Am I headed in the right direction with a dynamic sql in runtime? And how
do I put this together?

Please help. Thanks!

 

[Allen Browne]

What you are seeking to do is not a simple thing.

Part of the issue is what happens when you combine ANDs and ORs. If you ask
for:
(Like "*red*" or like "*white*") and like "*blue*"
anything that doesn't have Blue in the text is rejected, because BOTH parts
of the AND have to be fufilled.

However, if you ask for:
Like "*red*" or (like "*white*" and like "*blue*")
some records that don't have Blue will be returned. Just having Red
somewhere in the field is enough to satisify the OR.

Do you see that the bracketing returns different results? This is a major
pain when trying to design a really flexible interface that mixes both ANDs
and ORs.

So, here are some options:
a) Use Filter-By-Form
This button on the toolbar in Form view lets you enter multiple criteria
(treated as ANDs.) At the bottom of the FBF form, there is a tab where you
can enter another set of critera, and the 2nd set is treated as OR with the
first set.

b) Use the graphical query interface
In query design, all the items you place on a row of the Criteria are
treated as ANDs. The next set of critiera (on the next row) are treated as
OR with the preceding row. To do this in a form, go to:
Records | Filter | Advanced Filter/Sort

c) Write code for your own interface
This article contains an example of how to do a simple search form:
http://allenbrowne.com/ser-62.html
It covers ANDs only. Download the example, and pull it apart. Once you have
grasped how it works, you can then move on to the next step of how you
interface the bracketing for your mixed ANDs and ORs and other operators.