Access Cookbook - Ken Getz [66]
In the sample report, the maximum width of the rectangle is four inches. If a student has a score of 100%, you want the printed bar to be 4 inches wide. Therefore, the expression:
Me.txtScore/100 * 4
evaluates to the number of inches wide that you'd like the bar to be. To set the width of the bar from the Format event, however, you'll need to specify the width in twips, not inches, because that's what Access expects. There are 20 twips in a point and 72 points in an inch, so there are 1,440 twips in an inch. To convert the number of inches to twips, multiply the calculated value by 1,440. The final expression in the sample report is:
(Me.txtScore/100) * (1440 * 4)
This expression will evaluate to be the width of the bar in twips, which is exactly what you need. If your report needs a scaling factor other than 100 or a maximum width other than 4, you'll need to adjust the expression accordingly.
Though the method presented in this solution will work only for the simplest of cases, when it does work it does a great job. It's quick, it's simple, and it produces nice output. To achieve the effect you want, experiment with different shadings, border colors, and gaps between the rows.
3.7. Create a Page Total
Problem
Access allows you to create a group total in the group footer on a report or a report total on the report footer, but you can't find a way to create a page total in the page footer. You understand that this problem doesn't come up too often, but for your report you could really use this element. Is there a way to sum up values over a single page?
Solution
It's true that Access allows aggregate calculations only in group or report footers. You can, however, easily create page totals using two simple macros. This solution demonstrates this technique and shows how to add this capability to any of your own reports.
To create page totals for your own reports, follow these steps:
Create your report, and sort and group the data as desired. In the report's page footer section, include a text box named txtPageTotal.
Create the following event procedure in the Format event of the page header and report header sections:
Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.txtPageTotal = 0
End Sub
Private Sub ReportHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.txtPageTotal = 0
End Sub
Create an additional event procedure in the OnPrint event for the detail section:
Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
Me.txtPageTotal = Me.txtPageTotal + Me.Freight
End Sub
Save your report. When you run it, you will see the total of the field you set in the OnPrint event procedure.
Now load rptPageTotals from 03-07.MDB in preview view (see Figure 3-14). This report is used to track orders and their freight costs. The items are grouped by month, and each group has a total in the group footer. At the bottom of each page, you'll see the total for all items on the current page.
Figure 3-15 shows the sample report in design view.
Figure 3-14. Page 2 of the rptPageTotals report with page totals
Figure 3-15. rptPageTotals in design view
Discussion
Access makes it simple to sum values in group or report footers: use the Sum function in the ControlSource property for a text box. For example, to sum the freight costs in either a group footer or a report footer, you could use an expression like this:
=Sum([Freight])
and Access would perform the sum over the range included in the footer section (for either the group or the entire report). To create a page total, however, you must dig a bit deeper into the way Access prints reports.
The report-printing engine in Access works as a forward-marching machine: the engine formats and then prints each section in turn, such that each section is handled in the order in which it appears on the page. The report-printing engine deals first with the report header, then