Solved

Creating parameters for queries to ask for an input

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…

863 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

24 Experts available now in Live!

Get 1:1 Help Now