424,851 Members | 1,492 Online
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

# Trying to use the NZ Function with #num! as a result

P: 5
I have a form with a calculated field (Business #1 DCR)see attachment. The field has a data Control Source of: =([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1])with a Default Value of 0. Which works fine if there are numbers in the related fields, but produces #Num! if the related fields all have the default 0. I would like result to be blank or have a 0 as a result if the related fields contain the default 0. I have tried numerous Nz calculations but have not been successful. Anyone have any ideas how to resolve this? Any help would be greatly appreciated. Thanks

Attached Images
 FormPic.jpg (81.3 KB, 366 views)
Jan 30 '12 #1

You describe a situation where you have Divide by Zero errors (#Num!). This is entirely unrelated to nulls in any form.

To avoid these errors you need to check for a result of zero (0) in your divisor, for such a formula is incalculable. Use IIf() to do this, as in :
Expand|Select|Wrap|Line Numbers
1. =IIf([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]=0,0,([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]))