By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Query design - How to design one query which can use different inputs

P: 91
Hello,

Scenario:
I have list of policies for different countries. What I have done so far is that I have designed a query for each country which will pull out the policies for that country. The report is bases on this query. However, this way I am creating lot of queries.

What I want to do is design a query (and therefore the report) so that I can pull out the policies for a country by simply selecting a name from a drop down.

Tables and Fields are as below:

Table[Country]
CountryID
CountryName

Table[Policy]
PolicyID
CountryID
PolicyDetails

Could you please advise if this is possible and if yes, how to achieve this?

Thanks,
SG
Apr 13 '12 #1
Share this Question
Share on Google+
13 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Yes, that is quite easy.

You should design your report as you normally would, except you do not apply a filter on the country ID. Then you pass(and apply) the filter when you open the report.

Lets imagine you have a combobox in which you have selected your country, lets call it cmb_SelectCountry. You can now choose to either use the AfterUpdate event or make a button with code like so:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_PrintReport()
  2.   If IsNull(Me.cmb_SelectCountry) then
  3.     Msgbox "No country selected"
  4.     Exit Sub
  5.   End If
  6.  
  7.   docmd.OpenReport "rep_MyReportName",acViewPreview,,"ID_Country=" & me.cmb_SelectCountry,acDialog
  8. End Sub
Apr 13 '12 #2

P: 91
Thanks SmileyCoder.

Sorry,I am almost zero in VB coding :-(.

The way I am doing things are:

Step 1 - I am building the query first using Query Design. In the Criteria field, I am using the Country name for which I want the policies.
Step 2 - After saving the query, I am using Report Wizard based on the saved query to generate the report.

So, I am not using and Combobox at the moment (just using the Criteria in the Query design).

Given my situation, could you please walk me through how do I achive my objective?

Many thanks,
SG
Apr 13 '12 #3

100+
P: 759
In your query, in field CountryName, in the Criteria row write this: Country ?
Run the query, answer to question, and let me know if this help you.

Cheers

PS: Open the report too
Apr 14 '12 #4

P: 91
Thanks Mihail for your response. I tried what you suggested.

On hitting the run button, there is no question, it just shows a blank datasheet.

The field in the Criteria changes to [Like "Country ?"] after running the query.

Any further suggestion please.
Apr 16 '12 #5

P: 91
Hi Mihail,

Just to add, I simply put a "?" in the criteria field and then I see this question box popping up. Now wondering, how do I put some additional text so that it shows in the message box that's popping up?

Many thanks,
SG
Apr 16 '12 #6

P: 91
OK, got this too.. I inserted this as [Country ?], and now I can see it working :-)
Apr 16 '12 #7

P: 91
Many thanks for your help Mihail.
Apr 16 '12 #8

P: 91
Sorry to pepper with more questions.....

I just want to know that the solution that you have suggested above, how does it work when the table has more than 1 field?

For eg, in the table[Country], if there are more fields eg

[CountryID]
[CountryName]
[Region]
[etc..]

If I want to use the filter the information based on [Region] or any other field, will it still work?

Many thanks,
Apr 16 '12 #9

100+
P: 759
Try it !
Apr 16 '12 #10

P: 91
:-) It works for multi fields and multi tables as well, thanks.

The problem that I face is that the entry has to be same as that in the table and if the list is long, then it is not great to have everything in the pop- up.

Is there a way that I can show the available options using a drop down so that it is easier for any user to select and generate the report they want?

Thanks,
Apr 16 '12 #11

100+
P: 759
Yes it is.
See Smiley's solution.
Apr 16 '12 #12

P: 91
Many thanks. I can do this but I need little bit of hand holding here to implement Smiley's solution.

Can anyone walk me through the steps that I need to take?

Thanks.
Apr 16 '12 #13

P: 91
I tried the following as suggested by one of the gurus:

1. Create a blank form and add the Combobox/Dropdown control on it.
2. Follow the Wizard to display the names of the countries for the selection.
3. Create a new query (or modify one of the existing ones) to display all the policy data.
4. For the country criteria, enter a reference to the dropdown control on the form. It should look something like this:

Forms!FormName.ControlName

5. Create a new report (or use the same one based on the query you modified) based on the new query.
6. Add a button on the form and follow the Wizard to "open the report."

To test it, open the form and select a country from the dropdown, then click on the button.

This worked for me !
Apr 17 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.