Microsoft KB Archive/210261

= ACC2000: How to Sum a Column of Numbers in a Report by Page =

PSS ID Number: 210261

Article Last Modified on 7/31/2002

-

The information in this article applies to:


 * Microsoft Access 2000

-



This article was previously published under Q210261



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 2002 version of this article, see 296249.



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.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

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. Both of these methods are demonstrated by the examples below.

Creating the Report to Be Used in the Examples
 Open the sample database Northwind.mdb or sample project NorthwindCS.adp. Create a new report based on the Products table, click AutoReport: Tabular, select 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.</ol>

=== Example 1:

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 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.

=== Example 2:

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 click PageFooterSection. Click the arrow in the Event combo box located at the top-right side of the Module window, and 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 click ReportHeader. Click the arrow in the Event combo box located at the top-right side of the Module window, and click Print. Add the following code to the ReportHeader_Print event procedure. x = 0 </li> Close the Module window and 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">