Using Form for Query Criteria

[Chris]

I'd like to use a form to enter the query criteria for a
complex search. If I use a form, I can then print the
search criteria the user entered at the top of the report
with the results.

I want the ability to enter a complex logical search
string in the form, with the query picking that up just as
though it were entered directly in the criteria field in
the query.

For example, the user should be able to enter a search
criteria such as:

like "*"&"fred"&"*" or like "*"&"mary"&"*" or
like "*"&"sally"&"*"


Any ideas on how to do that? I've tried entering the
following in the criteria field of the query:

[forms]![formname]![fieldname]

but that doesn't work.

Thanks for any tips...

 

[John Vinson]

I want the ability to enter a complex logical search
string in the form, with the query picking that up just as
though it were entered directly in the criteria field in
the query.

You can't do this with a Parameter query - parameters work for the
actual searched values, but not for operators such as LIKE or AND.

You'll need to write VBA code to parse through the user's input and
build up a SQL string. Ensuring that the user enters valid, legal SQL
is a challenge; ensuring that they enter valid, legal, and CORRECT SQL
is a real gamble, unless you have some top-notch users!

 

[Kevin Sprinkel]

Chris,

I agree with John Vinson that error-checking for valid and
correct SQL probably is not a good solution, however, you
could build a form that allowed your user to enter
multiple strings. Your code could add the "*" and "like"
and "or". Or, for greater flexibility, for each string,
you could provide:

- an option group or dropdown box similar to the Access
Search dialog to match the Start of Field, Whole Field, or
Any Part of the Field
- the same for the logical operator AND or OR

For example, to create the search criteria,

like "*"&"fred"&"*" or like "*"&"mary"&"*"

your user would enter "fred", select Any Part of the
Field, and select "OR" on the first line, then
enter "mary", and select Any Part of the field for the
second line.

Your users get flexibility in specifying search criteria,
but you maintain control such that your code can build a
valid SQL string.

Hope it helps.

Kevin Sprinkel