About handling Null values in calculations

The aggregate functions won't include records containing blank (Null) values in their calculations. For example, the Count function returns a count of all the records without Null values. If you want to find the total number of records including those with Null values, use Count with the asterisk (*) wildcard character.

Count(*)

To count Null values when using the other aggregate functions, use the Nz function, which converts Null values to zeroes so they are included in a calculation.

FreightPercentage: Sum(Nz([Freight], 0)) / Sum(Nz([Subtotal]), 0) * 100

When you use an arithmetic operator (+, -, *, /) in an expression and the value of one of the fields in the expression is Null, the result of the entire expression will be Null. If some records in one of the fields you used in the expression might have a Null value, you can convert the Null value to zero using the Nz function as shown in the following example:

Nz([UnitsInStock], 0) + Nz([UnitsOnOrder], 0