Solved

Creating parameters for queries to ask for an input

Posted on 2013-01-11
4
310 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

717 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