Iif Statements

[Melinda]

I am working on a Budget Database, so I have 4 subreports linked/embedded on
the main report. The problem I am having is when the subreport does not
have data to bring to the main report it will not perform the calculation
correctly. In a nutshell I have the Encumbered Amount - Transfers +
Accounts Receivable - Purchase Orders Encumbered - Credit Card Payments.
If that paticular department does not have a Purchase Order Encumbered, my
calculation will give me a #Error. If I add in a purchase order encumberd
it preforms the calculation and provides the correct answer. This is the
calculation I am using on the report.

=Nz([subModybySAC].[Report]![txtTotalEncAmount],0)+Nz([subDebitVouchers].[Report]![txtGrandTotal],0).
If I change that and replace the Nz with a Iif it will not add the two
amounts together. What I need is to perform the calculation if there is
data or if there is no data. I have been staring at this for two days, any
help would be greatly appreciated.

 

[Marshall Barton]

I am working on a Budget Database, so I have 4 subreports linked/embedded on
the main report. The problem I am having is when the subreport does not
have data to bring to the main report it will not perform the calculation
correctly. In a nutshell I have the Encumbered Amount - Transfers +
Accounts Receivable - Purchase Orders Encumbered - Credit Card Payments.
If that paticular department does not have a Purchase Order Encumbered, my
calculation will give me a #Error. If I add in a purchase order encumberd
it preforms the calculation and provides the correct answer. This is the
calculation I am using on the report.

=Nz([subModybySAC].[Report]![txtTotalEncAmount],0)+Nz([subDebitVouchers].[Report]![txtGrandTotal],0).
If I change that and replace the Nz with a Iif it will not add the two
amounts together. What I need is to perform the calculation if there is
data or if there is no data.

Instead of
=Nz([subModybySAC].[Report]![txtTotalEncAmount],0) + ...

use this lind of check for a subreport with no data:

=IIf(subModybySAC.Report.HasData,
subModybySAC.Report!txtTotalEncAmount, 0) + ...