Report errors when one grouping is empty

Question

I am using Access with Microsoft 365.

I have a report that displays all open invoices and is grouped by client. The grouping footer has sum and average aggregates for the fields "invoiceAmount" and "DaysOS"

Everything works fine if every client has an open invoice. But if a single client does not have an open invoice the report will error with "Error no 3464 Data Type Mismatch in Criteria Expression"

The problem has to do with the Average aggregate, If it is removed the Report will not error.

The average aggregate is defined in a textbox control as =avg([DaysOS]). I tried to handle nulls with =avg(nz([DaysOS],0)) but the error persists.

How can I use the average aggregate in a report grouping when some groups will be empty?

Thanks

Dave

Answer 1

Neither one of those options works because the control source for the DaysOS text box was set up as =NZ(DaysOS, 0).

The problem is that these expressions reference a computed control , whose ControlSource property is itself an expression.  You cannot do that, you must repeat the original expression as the argument of the aggregation operator.

As regards your solution of returning zeros in place of Nulls in the query, are you sure that this gives you the correct average value?  I would have thought that the average should only be of the non Null values, which is how the AVG operator works, so should ignore the zeros which have replaced the Nulls.  If you use the AVG operator without returning zeros in place of Nulls, but with the reference to the computed control replaced by the original expression, you should find it works correctly.

Mathematically, treating a Null as zero makes little sense.  Null is the absence of a value, so could be anything from negative infinity to positive infinity.  The nearest we can get to a meaning for it is 'unknown', but it is really semantically ambiguous.  This is why aggregation operators exclude Nulls.  In reality we often have to decide whether, in a given situation, it should be regarded as zero or not.  In an expression involving the addition for instance (not to be confused with summation) of a fixed number of values, it is usually necessary to treat Nulls as zero because Null propagates in any arithmetical operation, i.e. the result will always be Null.

Answer 2

Thank you Scott. Unfortunately I did not fully describe the situation in my original post. It works now. See my response to Ken above.

Answer 3

Thanks for your help Ken.

Neither one of those options works because the control source for the DaysOS text box was set up as =NZ(DaysOS, 0).

I apologize for not making that clear in my original post.

Apparently the aggregate functions operate on values in the resultset fields before any manipulation by VBA in the form's controls. Since some of these values were NULL, the aggregates would error out even though the NULLs were converted to zeros for display on the form.

So the solution was simply to move the NZ function to the SELECT statement so the resultset came down to the form with the NULLs already converted to zeros.

Again, thank you for your help.

Answer 4

Do you need to show client with no open invoices in this report? If you don't then filter them out of the Recordsource for the report and the problem goes away.

Answer 5

What happens if you use the following to return the average of all non Null DaysOS values?

SUM(DaysOS)/COUNT(DaysOS)

Or the following to return the average, including Nulls, treating the latter as zeros:

SUM(DaysOS)/COUNT(\*)