Microsoft KB Archive/296249

= How to sum a column of numbers in a report by page in Access 2002 =

Article ID: 296249

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q296249



This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.



For a Microsoft Access 97 version of this article, see 132017.



SUMMARY
In a Microsoft Access report, you can sum a column of numbers by group or over the entire report, but there is no built-in way to print the sum of a column on each page of a report. This article shows you how you can use code to print the sum of a column of numbers on each page of a report. The code assumes the field that you are summing is a Number field.



MORE INFORMATION
You can print the sum of a column on each page of a report in two ways. You can sum the column of each page, and then add the total to the total of each subsequent page (a running sum). Or you can sum the column of each page separately.

Creating the Report to Be Used in the Examples
 Open the sample database Northwind.mdb or sample project NorthwindCS.adp. Create a new report that is based on the Products table, click AutoReport: Tabular, click the Products table, and then click OK. On the View menu, click Design View.  Delete the text boxes to the right of the UnitPrice text box. Add another text box to the detail section, and then enter the properties of the text box as follows:   Name: RunSum Control Source: UnitPrice Visible: No  Running Sum: Over All  Save the report as Report1.</li></ol>

Creating a Running Sum for the Entire Report with Subtotals on each Page

 * 1) Open the Report1 report in Design view.
 * 2) Add a text box to the page footer section of the report, and then set the ControlSource property of the text box to =runsum.
 * 3) Preview the report. Note that the running sum is displayed at the bottom of each page.

Summing a Column for Each Page Separately
<ol> Open the Report1 report in Design view.</li> Add a text box to the page footer section of the report, and then set the Name property of the text box to PageSum.</li>  On the View menu, click Code, and then type the following line in the Declarations section of the report module: Dim x As Double </li>  Click the arrow in the Object combo box located at the top-left side of the Module window, and then click PageFooterSection. Click the arrow in the Event combo box located at the top-right side of the Module window, and then click Print. Add the following code to the PageFooterSection_Print event procedure. Me!PageSum = Me!RunSum - x x = Me!RunSum </li>  Click the arrow in the Object combo box located at the top-left side of the Module window, and then click ReportHeader. Click the arrow in the Event combo box located at the top-right side of the Module window, and then click Print. Add the following code to the ReportHeader_Print event procedure. x = 0 </li> Close the Module window, and then preview the report. Note that the sum for the Unit Price for that page appears on each page of the report.</li></ol>

<div class="references_section">