Print report/subreports based on each date in range

[manning_news]

Using A2K. I've been asked to modify a report currently requiring only
one date parameter to now accept a date range. The main report has 2
subreports and is not bound to a table or query. The report prints
dental and hygenist appointments for the date (one subreport for each).
The user wants to enter a date range and have one page for each date
in the date range. I'm wondering how to modify the report. The only
way I see is to create a table with a record for each date in the date
range and base the main report on this table. Then link the subreports
on the date.

Is there an easier or better way? Thanks for any help or advice.

[Allen Browne]

A simpler approach would be to use a form where the user inputs the date
range, and have the queries for both the main report and the subreport read
the value from this form.

1. Create a form with text boxes named (say) StartDate and EndDate. Since
they are unbound, set the Format property of these boxes to Short Date so
Access knows they are dates, and won't accept an invalid date. Save the form
as (say) DateRange.

2. Open the main report's query in design view. In the Criteria row under
the date field, enter:
Between [Forms].[DateRange].[StartDate] And [Forms].[DateRange].[EndDate]

3. Still in query design view, choose Parameters on the Query menu, and type
the 2 names in there as well. Again, this prevents Access misunderstanding
the date type. The two rows in the narrow dialog will actually contain:
[Forms].[DateRange].[StartDate] Date/Time
[Forms].[DateRange].[EndDate] Date/Time
Save the query.

4. Repeat steps 2 and 3 for the subreport's query.

When the main report runs, it will read the dates from the form once. Then
each time the subreport is called (typically for every record in the main
report), the subreport will also read the dates from the form, and match the
range.

You will probably have to modify the existing report so that it no longer
matches on a single date. For example, you might find that setting in the
LinkMasterFields property of the subreport control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Using A2K. I've been asked to modify a report currently requiring only
one date parameter to now accept a date range. The main report has 2
subreports and is not bound to a table or query. The report prints
dental and hygenist appointments for the date (one subreport for each).
The user wants to enter a date range and have one page for each date
in the date range. I'm wondering how to modify the report. The only
way I see is to create a table with a record for each date in the date
range and base the main report on this table. Then link the subreports
on the date.

Is there an easier or better way? Thanks for any help or advice.

[manning_news]

Thanks for your reply. I'm not sure how to base the main report on a
query though, for this situation. Currently, there is no query for the
main report. The user enters a single date on a form. The subreports
then each have a query that uses that date as a parameter and pulls
data from their respective tables.

Sorry if I'm dense but I'm not understanding how a query on the main
report will help me.

[Allen Browne]

You already have a form with a text box for one date?
That's great. Open it in design view, and add the 2nd date.

Then open the query for the subreport.
You can see how it refers to the text box on the form?
Have it refer to the 2nd text box as well.
The previous answer gave an example.

If the main report is currently based directly on a table, you need to
create a query for it:
1. Create a new query into this table.
2. Add all the fields you need.
3. In the Criteria row, under the date field, use the same expression you
used for the subreport's criteria.
4. Save the query. Close.
5. Open the main report in design view.
6. Open the Properties box (View menu).
7. Change the RecordSource property of the report to the name of the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@hotmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

Thanks for your reply. I'm not sure how to base the main report on a
query though, for this situation. Currently, there is no query for the
main report. The user enters a single date on a form. The subreports
then each have a query that uses that date as a parameter and pulls
data from their respective tables.

Sorry if I'm dense but I'm not understanding how a query on the main
report will help me.