?
Solved

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

Posted on 2005-04-14
5
Medium Priority
?
693 Views
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:
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
0
Comment
Question by:jetera
  • 3
  • 2
5 Comments
 
LVL 26

Accepted Solution

by:
dannywareham earned 1600 total points
ID: 13782990
#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

by:jetera
ID: 13783058
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

by:dannywareham
ID: 13783079
Close, more like this:

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

Author Comment

by:jetera
ID: 13783207
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

by:dannywareham
ID: 13783256
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

809 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