# Help with #Num! error on a report

When I use a calculated field on a report and it is dividing a number by zero, it produces an error #Num! in the field on the report.
Is there a way to have it produce the answer whenever it is legit, but replace the #Num! with 0 or anything I decide.
I was thinking maybe IIF ?

Thanks

Bill
###### Who is Participating?

x

Commented:
=IIf([SumOfmay amt]=0," ",[SumOfmay margin]/[SumOfmay amt])
0

Commented:
here is an example of that:

field3: IIf([field2]=0;"0";[field1]/[field2])

cheers
Ricky
0

Commented:
paurths is on the money (not sure about those semi-colons though?)

i would change it around a bit to accept NULL values as well. such as:

CalculatedField : IIF(Nz(AFieldThatCouldBeZero,0) = 0, 'INF', SomeOtherField / AFieldThatCouldBeZero)

dovholuk
0

Author Commented:
I tried this:
=IIf([SumOfmay margin]/[SumOfmay amt]=0," ",[SumOfmay margin]/[SumOfmay amt])

and the answer is either the correct amt or I get a #Num!

I notice it is when both margin and amt = 0
Could it have something like division by 0 is not allowed?

Bill
0

Commented:
u checked the division in the iif statement to a 0 value.
if sumofmay amt is yet zero then u have #num result, so it is not 0 and the true part of the iif statement is not shown. Only the false.
0

Commented:
btw, exactly, division by zero can not be processed.
0

Commented:
or checking for null values also (dovholuk)
=IIf(Nz([sumOfMay amt];0)=0;" ";[SumOfMay margin]/[SumOfMay amt])
0

Author Commented:
That's it!

Thanks for the help.

Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.