Solved

Creating parameters for queries to ask for an input

Posted on 2013-01-11
4
295 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
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 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 92

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now