"#Div/0!" and "#Num!" and "#error" all on one report

Hi everyone,
I have a main report that calculates a percentage from a value on a sub report.  I am getting these three errors in different records.  "#Div/0!" and "#Num!" and "#error".
I understand that what they mean:
divide by zero error
zero divided by zero error
and a number divided by a record that doesn't exist.

How can I catch all three errors?  I have searched and found different things to try but nothing so far has caught all three errors.
My subreport field is called CatTotal or subyear_varianceactual.CatTotal.
The formula I have in the percentage text box (txtdollarvariance) now is:
IIf(nz([subYear_VarianceActual].Report!CatTotal),0,([txtdollarvariance]/[subYear_VarianceActual].Report!CatTotal)*100)
I think I need a couple of "or ifs" in there but I am getting all confused.

Thanks in advance,
Jetera
jeteraAsked:
Who is Participating?
 
dannywarehamConnect With a Mentor Commented:
#DIV/0 happens when you are dividing a number by zero.
#Num! happens when you are creating a sum with a text figure in there somewhere
#error is a general error message for when a name is not recognised or calculated correctly.

Your Iif statement has missing arguements:

Iif(Field condition value, if true, if false)

So, you need:

IIf(nz([subYear_VarianceActual].Report!CatTotal)=0,0,([txtdollarvariance]/[subYear_VarianceActual].Report!CatTotal)*100)
0
 
jeteraAuthor Commented:
Ok thanks - that takes care of the Num and Div/0 errors but it doesn't look after the #Error.  That one is coming from the subreport not having an actual record to correspond with the main report.
So then I tried to factor into the whole statement IIf (isError[field],0)
and I don't know how to factor it in to the expression, however it does work on its own - but then the other two errors come back.
Is it possible to fit that one in the IIF statement as well to catch the #Error?

Thanks :)
0
 
dannywarehamCommented:
Close, more like this:

Iif(iserror([yourfield]),0,[yourfield])
0
 
jeteraAuthor Commented:
Ok thanks dannywareham- it works now- I just got totally confused by each error and couldn't catch them all!  Thanks!
0
 
dannywarehamCommented:
Just remember with IF statements that there are always three arguements:

IIF (Field condition result, what if true, what if false)

So:

IIF([TODAY] = "THURSDAY", "Today is Thursday", "Today is not Thursday")

Start with this, then add in any error traps

:-)

Glad to have helped
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.

All Courses

From novice to tech pro — start learning today.