Solved

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

Posted on 2005-04-14
644 Views
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.

Jetera
0
Question by:jetera

LVL 26

Accepted Solution

#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

Author Comment

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

LVL 26

Expert Comment

Close, more like this:

Iif(iserror([yourfield]),0,[yourfield])
0

Author Comment

Ok thanks dannywareham- it works now- I just got totally confused by each error and couldn't catch them all!  Thanks!
0

LVL 26

Expert Comment

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")

:-)

0

## Featured Post

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…