Microsoft KB Archive/210458

= ACC2000: How to Exclude Zero Values When You Calculate Averages =

Article ID: 210458

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210458



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

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



SUMMARY
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 all the nonzero values in a set by counting the number of nonzero values in the set of values and then using that total with a running sum calculation.



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:  Open the sample database Northwind.mdb. In the Database window, click Reports, and then click New. In the New Report dialog box, click Report Wizard, and then click OK. 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. Click Next in the Report Wizard, and then click Grouping Options. In the Group-level fields box, type ProductID ; in the Grouping intervals box, click Normal.</li> Click OK, and then click Finish.</li> On the View menu, click Design View.</li> 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.</li> Set the RunningSum property of the text box to Over Group, and set its Name property to CountOfData.</li> Add another text box to the Report Footer section. This text box will display the result of the average calculation.</li> Set the ControlSource property of this text box to:

=Sum([Discount])/[CountOfData]

</li> Preview the report.</li></ol>

The left column displays a running count of nonzero (and non-Null) discounts, and the group footer displays an average discount based on the running count.

Example Two
This example uses a user-defined function that is the functional inverse of the NullToZero function in the Northwind sample database. <ol> Open the sample database Northwind.mdb.</li>  Create a module, and then type the following line in the Declarations section if it is not already there: Option Explicit </li>  Type the following procedure: Function ZeroToNull( MyValue As Variant) As Variant If MyValue = 0 Or MyValue = Null Then ZeroToNull = Null Else ZeroToNull = MyValue End If End Function </li> Repeat the steps in the "Example One" section, but use the following expression in place of the expression in Step 9:

=ZeroToNull([Discount])

</li></ol>

NOTE: You can also use the ZeroToNull function in a query.

<div class="references_section">