By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

Multiple Criteria Parameter Query

P: n/a
I'm trying to set up a parameter query based on an unbound form. This
search form has about 5 text boxes where the user can type in values
within each box and search a specific table based on the values of
those 5 text boxes. One of two of these boxes are based on date. The
obvious part (being the novice that I am) I'm stuck on is having the
query search based on 1 to 5 search values? In other words, I need the
query to search based on as little as 1 and as much as 5 different
search values from the search form.

I've tried using the "Like" operator(?) but I'm using "Between" as a
criteria for the date field and not quite sure if it's possible to use
a "Like" operator(?) with Between.

How can I get the query to work in cases where the user is not
searching based on all 5 search values.

Thank you for any help you can give me!!
dskillingstad

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<ds***********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I'm trying to set up a parameter query based on an unbound form. This
search form has about 5 text boxes where the user can type in values
within each box and search a specific table based on the values of
those 5 text boxes. One of two of these boxes are based on date. The
obvious part (being the novice that I am) I'm stuck on is having the
query search based on 1 to 5 search values? In other words, I need the
query to search based on as little as 1 and as much as 5 different
search values from the search form.

I've tried using the "Like" operator(?) but I'm using "Between" as a
criteria for the date field and not quite sure if it's possible to use
a "Like" operator(?) with Between.

How can I get the query to work in cases where the user is not
searching based on all 5 search values.

Thank you for any help you can give me!!
dskillingstad

In this sort of situation, you are probably not getting any benefit from the
fixed query parameters. I would abandon this approach and instead loop
through the 5 textboxes and build the sql statement up like that. Using DAO
you could then change the SQL property of the querydef object before you
open it. ie you have one query which you constantly re-write each time the
user clicks the search button.
Nov 13 '05 #2

P: n/a
DSKillingstad,
As the other reply says, you have to write some code to look at which text
boxes have values, whether the values supplied are valid, and if so, write a
WHERE clause for your SELECT statement based on the values supplied. One
issue that will come up is whether you arbitrarily decide the logical
operator for each supplied value or whether you will provide a means for the
user to indicate what they wanted. I follow the form given to users in
Excel and my dialog box offers a combo box of plain engilsh descriptions of
boolean operators and an option group to select either "and" or "or". Once
all this is working you won't need a PARAMETER clause in your select
statement because this finished custom form will produce a SELECT statement
that returns a result set based on what the user asked for.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

<ds***********@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I'm trying to set up a parameter query based on an unbound form. This
search form has about 5 text boxes where the user can type in values
within each box and search a specific table based on the values of
those 5 text boxes. One of two of these boxes are based on date. The
obvious part (being the novice that I am) I'm stuck on is having the
query search based on 1 to 5 search values? In other words, I need the
query to search based on as little as 1 and as much as 5 different
search values from the search form.

I've tried using the "Like" operator(?) but I'm using "Between" as a
criteria for the date field and not quite sure if it's possible to use
a "Like" operator(?) with Between.

How can I get the query to work in cases where the user is not
searching based on all 5 search values.

Thank you for any help you can give me!!
dskillingstad

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.