Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parameter Query - can you list multiple values?

Posted on 2011-02-12
9
Medium Priority
?
464 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:vsllc
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34880651
Hi vsllc,

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
0
 

Author Comment

by:vsllc
ID: 34880700
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.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34880723
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]
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 34880732
For an example of filtering by multiple selections in a multi-select listbox, see my Access Archon #197.  Here is a link for downloading it:

http://www.helenfeddema.com/Files/accarch197.zip

And here is a screen shot of the form:
Filtering-by-Listbox-Selections.jpg
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 34882595
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
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34884229
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.
0
 

Author Comment

by:vsllc
ID: 34885358
GRayL - What would the user input look like in the prompt?  Meaning how would they seperate the multiple values they wanted to search?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34888304
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.
0
 

Author Comment

by:vsllc
ID: 35118605
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?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question