vsllc
asked on
Parameter Query - can you list multiple values?
I have a parameter qry setup and working with a single value. I would like a user to be able to list multiple values within the prompt field, like you would in qry design using Or. Is it possible? I'm looking for them to enter exact values.
ASKER
Thanks Bill.
Unfortunately, there's 1,000's of possible selections which I think is too long for a combo or list box. Any alternatives?
If not, I may just have to go with the single value and see if it becomes a major, minor or no issue.
Unfortunately, there's 1,000's of possible selections which I think is too long for a combo or list box. Any alternatives?
If not, I may just have to go with the single value and see if it becomes a major, minor or no issue.
You might be able to set up cascading combo boxes or listboxes, where a selection in the first would filter the second. Here is a general description of this setup:
cboSelectCustomer has tblCustomers as its row source. Its AfterUpdate event sets cboSelectOrder to Null or "", and requeries cboSelectOrder.
cboSelectOrder has tblOrders as its row source, with a criterion of [Forms]![frmSelectOrder]![
cboSelectCustomer has tblCustomers as its row source. Its AfterUpdate event sets cboSelectOrder to Null or "", and requeries cboSelectOrder.
cboSelectOrder has tblOrders as its row source, with a criterion of [Forms]![frmSelectOrder]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi again,
Helen's solution is great. You can also use code in a form or a function to validate the user's input before the query is fired. You can then send the user a message if they've made a typo or something.
Bill
Helen's solution is great. You can also use code in a form or a function to validate the user's input before the query is fired. You can then send the user a message if they've made a typo or something.
Bill
Just so we're clear, you can do this:
SELECT * FROM myTable WHRE fld1 = enterv1 OR fld1 = enterv2 OR fld1 = enterv3;
or
SELECT * FROM myTable WHRE fld1 = enterv1 OR fld2 = enterv2 OR fld3 = enterv3;
I think you are limited to 99 OR clauses in a WHERE statement.
SELECT * FROM myTable WHRE fld1 = enterv1 OR fld1 = enterv2 OR fld1 = enterv3;
or
SELECT * FROM myTable WHRE fld1 = enterv1 OR fld2 = enterv2 OR fld3 = enterv3;
I think you are limited to 99 OR clauses in a WHERE statement.
ASKER
GRayL - What would the user input look like in the prompt? Meaning how would they seperate the multiple values they wanted to search?
I'm not sure I understand your question? For each parameter you will get a popup entitled "Enter Parameter Value", under which will be the parameter name you assigned - enterv1, or enterv1, or enterv3 as in my example above. After entering the responses, the query will produce a recordset returning the values of your parameters. It is to say you must know beforehand what fields you want to use, and the construct of the WHERE clause you need to produce the required result.
ASKER
OK. So if the parameter box said "What day of the week?", the user would key in "Monday, or Wednesday" in order to get results for just those two values?
It's best to create a form with a combo box or list box and have the users select the entry then fire the query from their selection.
Regards,
Bill