how to set empty fields to "0" in report?

[dmbfan715]

Hi,

I'm adding 2 fields in a report. The fields are "blank", or empty strings, if there is no calculation done for that record (complicated queries behind it). If one of the two fields is "blank", then the sum is also blank, when it should really return the value of the "nonblank" field. Is my explanation too confusing? How can I set the "blanks" to zeros, so the correct sum is calculated? I tried the IIF using both IsNull and IsEmpty, but that's not working:

IIF( IsNull([CapacityExposure]), "0", [CapacityExposure])

IIF( IsEmpty([CapacityExposure]), "0", [CapacityExposure])

Any suggestions? Thanks in advance!

Shivani

 

[grnzbra]

use the NZ function.

 

[dmbfan715]

Thanks grnzbra, but the NZ function isn't working either: this is what I had:

=IIf(NZ([Credit Exp @100%]),"0",[Credit Exp @100%])

I'm still getting #error in the [Credit Exp @100%] field in my report...

Any other suggestions?

 

[CosmoKramer]

Shivani,

Try:

=Nz([Credit Exp @100%], 0)