Prompting for parameters in a query

  • Thread starter Thread starter Baard Dahl
  • Start date Start date
B

Baard Dahl

Hallo,

I have a report that is based on four other queries. I
will normally not want to include all the data in the
database in the report, so there is one field (Country) in
all 4 queries that I use to limit the report.

The problem is, that if I want to run the report for one
specific country, I have to enter this in all four
queries, which is not very elegant and a bit time
consuming (I am lazy).

Is there a way to get ONE dialogue box prompting for which
country to run the report? Sometimes I may want to run one
report and include more than one country. Could that be
done?

Would be thankful for any assistance.

Baard Dahl
 
Try by creating new query based on that four querys - with parameter
(Country). Delete parameter query in this four querys, and link report to
new query. Second question can be done with new query without paramateters
and with new report. And all this can be done quit more elegance with
dialog form where you can put some text boxes and option buttons for
filtering data.
 
I'd encourage you to create a form for this report. The advantage of
this is that you will know they got the country code correct if they
have to select it from a list.

Put a listbox (Multi-select: None) on your form to display the
countries, and two command buttons Report and Cancel.

If you are only going to run the report for one country at a time,
then you need to modify your queries to reference this listbox rather
than the [Enter Country] parameter you are currently using.

If you want to run the report for multiple countries, modify the
Multi-select property of the listbox to simple or extended. From
here, one technique would be to add a field (ReportThis:Yes/No) to you
Countries table. After the user selects the country(s) to generate
the report on, they will need to click on a command button to generate
the report. When they do so, set the value of ReportThis to False for
all the countries. Then loop thru the list of selected countries and
set their ReportThis value to True. Then modify your queries so the
where clauses include something like shown below.

WHERE CountryID IN (SELECT CountryID FROM tblCountries WHERE
ReportThis)

If your application is multi-user, and you have split your database.
You should probably put a table in the FrontEnd db for this purpose,
since two users could feasibly run the report at the same time and
cause conflicts with this precedure.

--
HTH

Dale Fye


Hallo,

I have a report that is based on four other queries. I
will normally not want to include all the data in the
database in the report, so there is one field (Country) in
all 4 queries that I use to limit the report.

The problem is, that if I want to run the report for one
specific country, I have to enter this in all four
queries, which is not very elegant and a bit time
consuming (I am lazy).

Is there a way to get ONE dialogue box prompting for which
country to run the report? Sometimes I may want to run one
report and include more than one country. Could that be
done?

Would be thankful for any assistance.

Baard Dahl
 
Back
Top