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

Posted on 2005-04-14
Last Modified: 2008-02-26
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:
I think I need a couple of "or ifs" in there but I am getting all confused.

Thanks in advance,
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:


    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 :)
    LVL 26

    Expert Comment

    Close, more like this:


    Author Comment

    Ok thanks dannywareham- it works now- I just got totally confused by each error and couldn't catch them all!  Thanks!
    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)


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

    Start with this, then add in any error traps


    Glad to have helped

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    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…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now