Sum and IIF

I have a textbox on my report with this as the control source...
=Sum([DiscountCalc]+[Tax1Calc]+[Tax2Calc])

If the textbox is empty (or null) it displays '#Error'
I would like it to display '0'

Can I use an IIF statement with the Sum?

Thanks
Tom

I'd try Nz() rather than IIf().

I gave this a try, but to no avail.

=Nz(Sum([SumOfPrice]-([SumOfPrice]*[Discount]*0.01)),0)

Tom

The Nz() function needs to have the table field as its argument rather than the result of the Sum() function.

=Sum(Nz([SumOfPrice],0) - (Nz([SumOfPrice],0) * Nz([Discount],0) * 0.01))

This would also work:

= Sum([SumOfPrice]) - (Sum([SumOfPrice]) * Sum([Discount]) * 0.01)

The second statement works because aggregate functions handle nulls without a problem. The problem is only caused because you were doing arithmetic on the columns before the aggregate function processed them.

I have a feeling however (based on the names used in your second example) that you don't need to use an aggregate function at all. Is this what you need to do?

=Nz([SumOfPrice],0) - (Nz([SumOfPrice],0) * Nz([Discount],0) * 0.01)

Thank you for the reply Pat.

My report is getting quite complex, but in a nutshell, the form's controls source query has the fields Cost and Discount.
The query Sums Cost and renames it SumOfCost.

I have the report grouped. One group is PaymentType.
In the PaymentType footer I add all the SumOfCosts together with Discount in the below calculation.

=Sum([SumOfPrice]-([SumOfPrice]*[Discount]*0.01))

SumOfCost and Discount are in the reports detail section.
I have tried the Nz and IIF functions in the detail. Example:
textbox control source =Nz([SumOfCost],0)

When the query is empty I get the #Error display in the PaymentType footer textboxes.

So, yes I need the aggregate function.
And alas, the code you suggested didn't display a zero for me either.

Tom

=Iif(IsNumeric([SumOfCost]),[SumOfCost],0)