Expression writing

[HeatherM]

I have a report which has two subreports. I have a
calculated field which subtracts the amount in one
subreport from the amount shown in the other subreport.
This works well, but when one of the subreports returns a
blank the calculated field returns #Error. What I would
like is for the expression to treat the blank field as a
zero and return either a positive or negative result.

The expression in the calculated field is:
=[Projectsubrep3].[Report]![Text8]-[Projectsubrep1].
[Report]![Text8]
The [Text8] in each subreport is in itself a calculated
control in each case it is: Sum([Amount]) Should I adapt
this expression to always return either zero or a number?
If so how?

 

[Marshall Barton]

I have a report which has two subreports. I have a
calculated field which subtracts the amount in one
subreport from the amount shown in the other subreport.
This works well, but when one of the subreports returns a
blank the calculated field returns #Error. What I would
like is for the expression to treat the blank field as a
zero and return either a positive or negative result.

The expression in the calculated field is:
=[Projectsubrep3].[Report]![Text8]-[Projectsubrep1].
[Report]![Text8]
The [Text8] in each subreport is in itself a calculated
control in each case it is: Sum([Amount]) Should I adapt
this expression to always return either zero or a number?
If so how?

=IIf(Projectsubrep3.Report.HasData,
Projectsubrep3.Report!Text8, 0) -
IIf(Projectsubrep1.Report.HasData,
Projectsubrep1.Report!Text8, 0)

 

[HeatherM]

Brilliant!
Thank you

Heather

 

[Fredg]

Heather,
Look up the Nz() function in Access help.
=Nz([Field1],0) + Nz(Field2],0)