one report can use many queies

F

Frank Situmorang

Hello,

I want to use the form to filter 11 dates ( such as DOB, date of marriage,
etc)
I already designed a report to show who is born in Jan, Feb etc...

Instead of designed 11 reports, Is there any way to have only one report,
but we can have option to attach the query to this one report.

I have to look for another way, since all the dates are in the member table.

Thanks for any idea.
 
K

karl dewey

If the fields for all queries are the same or you can establish a set of
generic field names suitable for the queries then a union query can do it.
The union query would use criteria from a combo box or check boxes to
determine which part of the union query selects records.
 
B

Bill

You can pass the filter expression in the DoCmd.OpenReport.
Or, you can set the reports source in the OnOpen event.
Whatever you do, be sure to have one report and simply
change the reports data source when you run the report.
Bill
 
F

Frank Situmorang

How can we do it Bill. Let me explain what I have:

I have the form for filtering the date by filling in startdate and end date.
then I have an open button, to open report.
First report based on query in which query on the field " Date of Birth" I
put criteria:Between [Forms]![frmReportDates]![txtBegDate] And
[Forms]![frmReportDates]![txtEndDate].

As I said I have 11 dates...date of marriage..date of death...etc.. for date
of Marriage I will make another query and I put the criteria under filed date
of marriage. I want to avoide 11 reports and 11 queries. How can I make it
just 1 report but we can dynamically chaged the query attached to this report.

I will try to do this, since the structure of my table for dates is put in
the member table, so according to many experts in this news group, it is
hard, but I do not want to set back,since I almost finish 95%.

Thanks for any help.

Frank
 
K

karl dewey

Add 11 checkboxes to your form.
Add a field like this to the query grid in design view --
X : -1
Then put your ---
Between [Forms]![frmReportDates]![txtBegDate] And
[Forms]![frmReportDates]![txtEndDate]
in criteria row under the first date.
In the same criteria row under "X" field put ---
[Forms]![frmReportDates]![CheckBox1]

Then put your ---
Between [Forms]![frmReportDates]![txtBegDate] And
[Forms]![frmReportDates]![txtEndDate]
in next criteria row under the second date.
In the same criteria row under "X" field put ---
[Forms]![frmReportDates]![CheckBox2]

Repeat the above for each date but use a different criteria row for each.

--
KARL DEWEY
Build a little - Test a little


Frank Situmorang said:
How can we do it Bill. Let me explain what I have:

I have the form for filtering the date by filling in startdate and end date.
then I have an open button, to open report.
First report based on query in which query on the field " Date of Birth" I
put criteria:Between [Forms]![frmReportDates]![txtBegDate] And
[Forms]![frmReportDates]![txtEndDate].

As I said I have 11 dates...date of marriage..date of death...etc.. for date
of Marriage I will make another query and I put the criteria under filed date
of marriage. I want to avoide 11 reports and 11 queries. How can I make it
just 1 report but we can dynamically chaged the query attached to this report.

I will try to do this, since the structure of my table for dates is put in
the member table, so according to many experts in this news group, it is
hard, but I do not want to set back,since I almost finish 95%.

Thanks for any help.

Frank



Bill said:
You can pass the filter expression in the DoCmd.OpenReport.
Or, you can set the reports source in the OnOpen event.
Whatever you do, be sure to have one report and simply
change the reports data source when you run the report.
Bill
 
B

Bill

Do you have a command button for each report?
Or, are you wanting to generate all 11 reports with
a single command button action. If the latter is the
case, then the OnClick sub for the command button
will have to loop on each of the field names involved
in the report, but I'm assuming the date range is
the same for each report.

All add more to my post after you've answered my
question and confirmed the date range assumption.

Bill


Frank Situmorang said:
How can we do it Bill. Let me explain what I have:

I have the form for filtering the date by filling in startdate and end
date.
then I have an open button, to open report.
First report based on query in which query on the field " Date of Birth" I
put criteria:Between [Forms]![frmReportDates]![txtBegDate] And
[Forms]![frmReportDates]![txtEndDate].

As I said I have 11 dates...date of marriage..date of death...etc.. for
date
of Marriage I will make another query and I put the criteria under filed
date
of marriage. I want to avoide 11 reports and 11 queries. How can I make it
just 1 report but we can dynamically chaged the query attached to this
report.

I will try to do this, since the structure of my table for dates is put in
the member table, so according to many experts in this news group, it is
hard, but I do not want to set back,since I almost finish 95%.

Thanks for any help.

Frank



Bill said:
You can pass the filter expression in the DoCmd.OpenReport.
Or, you can set the reports source in the OnOpen event.
Whatever you do, be sure to have one report and simply
change the reports data source when you run the report.
Bill
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top