Dividing with 0

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I'm trying to run a report to get percentages, but some of the values are
zero. I'm not that familiar with Access and am learning as I go. When I use
the following formula, if both values have a number, it works fine. But if
one of the values is 0, then I'll either get a #Div/0! or #Num! error,
depending on whether one or both are 0 values. Here's the formula:
=(Sum(IIf(Now()>[Site Start Date]+30,"1","0"))/(Sum(([IJCom])*-1)))
(Multiplying by the -1 at the end gives me a positive number. Don't know any
other way.)
If any of this makes sense to anyone, will you please tell me how to do it?
I'm ready to pull out what little hair I have! Thanks.
 
I don't know what your formula is about but just add more IIFs.

Depending on what your query is about, you be able to do some of the sums in
your Query to make things clearer
eg have a calculated field which says
TestDate: -(Now()>([site start date]+30)

the leading minus will give a 1 if the statement is true and a 0 if it is
false.

Now you could have
IIF(Sum([TestDate])<>0,IIF(Sum([IJCom])<>0,sum([TestDate])/(-Sum(IJCom]),0))

Sorry, haven't tried this out so you may have to add more/less closing
parentheses.

This says basically, that there are 2 conditions before you can do the
division, both the Sum of TestDate and the Sum of IJCom have to be greater
than 0.

If they are not both more than 0 then the result you will get is 0 (if this
isn't what you want, then replace that last 0 with another number)

Evi
 
Try the following

=Abs(Sum(Now()>[Site Start Date]+30)/
IIF(Sum([IJCom])=0,Null,Sum(IJCOm]))

Abs is the absolute function, so that will strip the sign off the result.

If the sum of IJCom is zero, what do you want returned? I guessed that
you wanted a blank (null) returned, so I divide by Null. Null in an
arithmetic expression propagates to return Null.

1 + 2 + Null => Null
353 * Null => Null

If you want some other result, that can be accomplished.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Tim,
John Spencer's post is the correct answer to your question. I would add
just a bit of info. No computer ever designed can divide by 0, nor can any
calculator. That is because it is a mathmatical impossibility. To avoid
this possibility, it is always best when doing any division calculation to
trap for a 0 divisor. What you do in that case depends on the business rules
surrounding the calculation.
 
Back
Top