ACC: How to Sum a Calculation in a Report

1. Add a text box control to the report's OrderID group footer section to calculate a control that charges freight only when the order is over $100. Set the following properties for the control.
   
   NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
   

         Name: Freight
         ControlSource: =IIf(Sum([UnitPrice]*[Quantity])>100,0.05*Sum([Unit _
         Price]*[Quantity]),0)
   						

   
   NOTE: In Microsoft Access 1.x and 2.0, there is a space in [Unit Price].
2. Add a second text box control to the report's Order ID group footer section to calculate the running sum for this control. Set the following properties for the control.
   
   NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
   

         Name: FreightRunSum
         ControlSource: =IIf(Sum([UnitPrice]*[Quantity])>100,0.05*Sum([Unit _
         Price]*[Quantity]),0)
         RunningSum: Over Group
         Visible: No
   						

   
   NOTE: When you test a running sum, set the Visible property to Yes to verify that the control is accumulating. Once you verify that it does, hide the control by setting the Visible property to No.
3. Add a text box control to the report's footer section to display the total by referencing the RunningSum property. Set the following properties for the control:
   

         Name: Freight Total
         ControlSource: =[FreightRunSum]
   						

   
   NOTE: When you reference another control, use the Val() function to convert from a Variant to a number so that the running sum accumulates the value. For more information about running sums not accumulating, please see the following article in the Microsoft Knowledge Base:
   
   115877 ACC2: RunningSum Text Box in Report Not Accumulating Values
   
   
4. Preview the report to see the sum of the calculation.

Method 3: Summing Controls Calculated by Functions

1. Create a new module and enter the following code in the Declarations section:
   

         Option Explicit
         Dim OrderTotal As Double       ' Used for group total.
         Dim GrandTotal As Double       ' Used for report total.
   						

   
   
2. Add the following procedure to calculate the product of two fields:
   

         Function CalcProduct (R as Report)
            Dim tmpAmount As Double
            tmpAmount = R![UnitPrice] * R![Quantity]
            ' Remember the total for the group.
            OrderTotal = OrderTotal + tmpAmount
            ' Remember the total for the report.
            GrandTotal = GrandTotal + tmpAmount
            CalcProduct = tmpAmount
         End Function
   						

3. Add the following procedure to initialize the report's variables to 0:
   

         Function InitVars ()
            OrderTotal = 0
            GrandTotal = 0
         End Function
   						

4. Add the following procedure to retrieve the total for the group:
   

         Function GetGroupTotal ()
            GetGroupTotal = OrderTotal
            ' Reset the variable to 0 for next group.
            OrderTotal = 0
         End Function
   						

5. Add the following procedure to retrieve the total for the report:
   

         Function GetReportTotal ()
            GetReportTotal = GrandTotal
         End Function
   						

6. To initialize the variables to 0, set the report's OnOpen property to
   

         OnOpen: =InitVars()
   						

   
   and then Add three text box controls to the report's detail section. Note that the Name property is the ControlName property in Microsoft Access 1.x. Set the following properties for the text boxes:
   

         Text box 1:
            Name: UnitPrice
            ControlSource: UnitPrice
   						

   
   NOTE: In Microsoft Access 1.x and 2.0, there is a space in Unit Price.
   

         Text box 2:
            Name: Quantity
            ControlSource: Quantity
   
         Text box 3:
            Name: TheProduct
            ControlSource: =CalcProduct([Report])
   						

   
   
7. Add a text box control to the group footer section to display the total for the group. Set the following properties for the control:
   

         Name: GroupTotal
         ControlSource: =GetGroupTotal()
   						

   
   
8. Add a text box control to the report's footer section to display the total for the report. Set the following properties for the control:
   

         Name: ReportTotal
         ControlSource: =GetReportTotal()
   						

   
   
9. Preview the report to see the sum of the calculation.