Solved

Creating parameters for queries to ask for an input

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

809 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