Hi,
This fomrula is in the control source of each unbound box in the
report and I'm looking to re-write this formula to cover for the #Div/
0! error and I'm not getting anywhere fast. This is in a YTD report so
each month box has this formula but for each month. I tried to add the
'IsError' function but I couldn't getting it right. Anybody got a
resolve so that the formula will place a 0(zero) in the box if the
calc produces a #Div/0! error.

=Nz(Sum(IIf([MthFor]="jan" And [EntryType]="l",[LODamages],0))/
Sum(IIf([MthFor]="jan" And [EntryType]="l",[WalkAway],0)),0)

thanks
bobh.

bobh wrote:

Sum(IIf([MthFor]="jan" And [EntryType]="l",[WalkAway],0))
value is zero or not before calcing (dividing).

I might consider, not having seen you report, having a secondary totals
query on month and entry type. Then you could use Dlookup() or some
flavor of it.

On 18/08/2010 21:07:37, Salad wrote:
Try IIf(Sum(IIf([MthFor]="jan" And [EntryType]="l",[WalkAway],0)) = 0,"-",
Nz(Sum(IIf([MthFor]="jan" And [EntryType]="l",[LODamages],0))/ >>
Sum(IIf([MthFor]="jan" And [EntryType]="l",[WalkAway],0)),0))

Hope that shows a dash if Sum(IIf([MthFor]="jan" And
[EntryType]="l",[WalkAway],0)) = 0 and does the calculation if it is not zero

Phil

On Aug 19, 4:38*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
That's works, thanks Phil
bobh.

On Aug 18, 4:07*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
actually I was hoping to trap the Div/o error at calculation time and
display a 0 in it's place, if that makes any sense.
I also had thoughts about totals query and storing the results in a
temp table and making the temp table the record source of the report
but that in my thinking was alot of stuff to do so my first idea was
to modify the existing report formula to capture the Div/0 error and
replace it with a 0.
bobh.

On 20/08/2010 16:54:37, bobh wrote:
Well where I have the "-" in the IIf clause, you can substitute what you want
from "0" to "No sales for " & MthFor

On Aug 20, 12:31*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
I did..... replaced it with "0.00%"
thanks

On 20/08/2010 21:23:19, bobh wrote:
So if you're january sales are 0 (MthFor]="jan") and WalkAway not = 0 then
the division will take place and the answer will be 0 That is why I suggested
a different symbol ("-" or "No sales for January) to differentiate between a
zero or non zero value of WalkAway

Phil

On Aug 20, 2:53*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
isn't anything / 0, infinity
and isn't infinity, 100%

On Aug 20, 4:53*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
I understand what you're saying but in this case, for this report,
it's not necessary to distinguish between them, the users wanted to
see a 0.00% if their side of the calculation had a zero(0) value. Be
it right or wrong, good or bad that's what the customer wanted so
that's what there getting.
bobh