?
Solved

Creating parameters for queries to ask for an input

Posted on 2013-01-11
4
Medium Priority
?
315 Views
Last Modified: 2013-01-11
I have a query that i am trying to obtain specific data so i created a parameter so when the query is ran, it asks for an input.  I understand how to do this, but is it possible to have multiple parameters for one field.  

For instance, i have a field called region and the data options are R01, R02 all the way to R11.  I want to be able to retrieve data for multiple regions, but i don't know how to do this on the design view.  I thought i could just do [Enter Region] several times within the design field, but it doesn't work.  

I have attached a screen shot of what i have in the design field.  I event tried using the between option as i have done with dates(Between [Start] And [End]), but that didn't work either. it just came up with blank data.  

Also, is there a way to make that prompt a drop down field so the individual can select versus manually typing in the data in case they didn't know what the field options were.  I realize this would not be able to pull multiple regions.  

Let me know if you have any questions or if you need a sample database.
Doc1.docx
0
Comment
Question by:jsawicki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38768562
what you need to do is create a query without criteria, use the query as Record Source of a data sheet form.

create a single view Form with Listbox
set the RowSource of the listbox to values of regions

now

drag and drop the datasheet form to the single form, to use it as a subform


use the listbox to filter the records shown in the subform


do this first, then

upload what you have created, and we'll continue in filtering the subform records


see this demo


http://office.microsoft.com/en-us/access-help/watch-this-create-a-form-that-contains-a-subdatasheet-HA010277407.aspx
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38768576
If the possible values are always R01, R02, R03, ..., R11, then one option would be to change your criterion to:

Like [Enter Region] & "*"

Then, to do regions 1, 2, and 7, you would enter this at the prompt:

R0[127]

To do all regions, leave blank or enter an asterisk.

Where it would get tricky would be something like 2, 3, and 11; I don't think a like expression could be crafted to handle that.

At that point, you would be better off:
1) Creating a form with a listbox with all the choices
2) Use VBA code to dynamically craft the SQL statement based on selections from the listbox
0
 

Author Closing Comment

by:jsawicki
ID: 38768577
This is perfect and in fact, we have a similar set up with a different database that someone created in my agency so i will just use that as reference long with the tutorial.  Thanks for the guidance.
0
 

Author Comment

by:jsawicki
ID: 38768920
Matt, thank you for your response since it worked great, just need to figure out the 10 and 11 as you stated.  Sorry i already closed out and didn't award you any points.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 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