Discussion Locked

Access 97 Reports - grouping with page b

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Using Access 97 - I would like to run a report that is grouped by stryear (such as 1998, 1999, 2000). The report is set up in 2 columns with the subtotal for each year created in the stryear grouped footer. I would like each year to print on separate pages, so I choose to "force a new page" property and set it to "after section" in that same group footer as the subtotal. This works fine and dandy for the stryears, but I would also like to have the Grand total (created in the Report Footer) to appear on the same page as the last stryear (say 2000). Currently, because of the force new page in the grouped footer, I have the Grand total printing on a separate page.

Anyone have any suggestions (especially ones that do not involve modules?).I hope to teach this an Advanced Reports class, but modules would be beyond the audience.

Thanks in advance!

8 replies

by linda b.

The first thing that comes to mind, outside of modules/expression builders is to force the new page "before this section" instead of after. This will keep your last header, page and report totals on the same page (if space allows).

You may also want to a page header instead of a report header if you want it to print on each page. If not, the first page will print only report headers/footers.

Good Luck,
Linda B.

by cheryl messer

I would like to keep the report header on the same page as the first grouped year, so this does not help. Thanks for the suggestion though.

by m_ozog

My answer is similar to the 1 above except that you put the "break before section" in the group header otherwise the details print on a seperate page from the subtotals.

by beckerthetrekker

You could put the Grand Total in the Report Footer. Then it would appear at the bottom of the last page. I did this experiment with a Report grouped on one field with a group header, but no group footer, grouped on each value, with a group interval of one, kept together with the First Detail.

by cheryl messer

The Report Footer does not come at the bottom of the last page of the report, it comes directly after the grouped footer, so that does not work. Thanks for the suggestion.

by is

Cheryl,

You will need to work with VBA and write your own event procedure to make the page break in the year footer visible. Try something like this:

***Begin Code***
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

IfMe.eof = "true" Then
Me.visible.pagebreak(you will have to insert appropriate name here) = 0
Else
Me.visible.pagebreak(you will have to insert appropriate name here) = -1

End If

End Sub

***End Code***

This should turn the Visible property of the page break off for the last record so that it will print your grand totals on the last page. You may have to play with the code a little but it is pretty close to what you need.

This discussion is closed to new replies.