You are currently offline, waiting for your internet to reconnect

ACC2000: How to Exclude Zero Values When You Calculate Averages

This article was previously published under Q210458
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

When you use the Avg() function in a report to average a set of values, the function uses records that have zero values in the calculation. Sometimes, you do not want to include records with zero values in a calculation.

This article has two examples of how to calculate an average for allthe nonzero values in a set by counting the number of nonzero values inthe set of values and then using that total with a running sumcalculation.

Example One

In this example, one text box will display the number of nonzero values, and the other text box will display the average for the set:
  1. Open the sample database Northwind.mdb.
  2. In the Database window, click Reports, and then click New.
  3. In the New Report dialog box, click Report Wizard, and then click OK.
  4. Use the Report Wizard to create a new Groups/Totals report based on the Order Details table. This report calculates the average discount for each product ID.

    Include the ProductID and Discount fields on the report.
  5. Click Next in the Report Wizard, and then click Grouping Options.
  6. In the Group-level fields box, type ProductID; in the Grouping intervals box, click Normal.
  7. Click OK, and then click Finish.
  8. On the View menu, click Design View.
  9. Add an unbound text box to the Detail section of the report. Position the text box to the left of the Discount text box. The new text box will display the count of nonzero values. Set the ControlSource property of the text box to:
    =IIf([Discount]=0 or [Discount] is null,0,1)
    This expression returns 0 if the value of the Discount field is equal to zero or Null; otherwise, it returns 1. The Avg() function automatically excludes Null values as well.
  10. Set the RunningSum property of the text box to Over Group, and set its Name property to CountOfData.
  11. Add another text box to the Report Footer section. This text box will display the result of the average calculation.
  12. Set the ControlSource property of this text box to:
  13. Preview the report.
The left column displays a running count of nonzero (and non-Null)discounts, and the group footer displays an average discount based on therunning count.

Example Two

This example uses a user-defined function that is the functional inverse ofthe NullToZero() function in the Northwind sample database.
  1. Open the sample database Northwind.mdb.
  2. Create a module, and then type the following line in the Declarations section if it is not already there:
    Option Explicit					
  3. Type the following procedure:
    Function ZeroToNull( MyValue As Variant) As Variant   If MyValue = 0 Or MyValue = Null Then      ZeroToNull = Null   Else      ZeroToNull = MyValue   End IfEnd Function					
  4. Repeat the steps in the "Example One" section, but use the following expression in place of the expression in Step 9:
NOTE: You can also use the ZeroToNull() function in a query.
For more information about the Avg() function, click Microsoft Access Help on the Help menu, type avg function (microsoft jet sql) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
nonzero pure

Article ID: 210458 - Last Review: 12/05/2015 11:32:11 - Revision: 2.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto kbinfo KB210458