Using Totals on Zero Values

[ChrisSedgwick]

Hi,

I'm hoping that you can help me with my issue.

I'm trying to use the Totals function in Reports to total Sum our Quote Values. The field that is being totalled is from a calculation in a query that makes up the report itself.

The calculation in the query works out the ContributionValue divided by the DurationOfOurWorks to give us a figure of how much contribution we can expect each month.

Back to the report, I now want to total these figures up to give me an overall monthly figure. However, not all of my records have data entered into either the DurationOfWorks or ContributionValue, therefore in some case, next to the individual records on the report it displays #Div/0! or is blank. Which means that when I try to use the Totals function to total them all up it displays this error...

"Division by zero"

Can anyone help me with a work around? The blank fields and the ones that display #Div/0! are correct, this just means they haven't had this information entered yet.

Hopefully someone can offer some advice?

Thanks in advance!

Chris.

 

[plog]

You're bumping up against math on this one, not Access. What would you like to display when you try to divide by 0?

Whatever the case, you would use an IIf statement to work around this. You would test the denominator for 0, then do the math if its not and show whatever you want if it is:

Iif([denominator]=0, "No Data", Cdbl([numerator]/[denominator]))

Now, for the blank records you want to make contain a value, you would use NZ function (http://www.techonthenet.com/access/functions/advanced/nz.php).