Totals Query And Division By Zero

  • Thread starter Thread starter Alan B. Densky
  • Start date Start date
A

Alan B. Densky

Hello,

This is a calculated field in my totals query:

WriteOffPerUnit: CCur(Nz(Sum([ReservesLoss])/(Sum(IIf([EndType]="CH" Or
[EndType]="NC" Or [EndType]="IN",1,0))),0))

The query works correctly except:
My problem is that if the record source HAS a record with an EndType of
"PO", but it DOESN'T have a record with an end type of "CH", "IN", or "NC",
the divisor for the Sum([ReserveLoss]) ends up being zero. And as we all
know, that's a no-no. I need the answer in this case to be zero.

Can somebody please help me re-write this calculated field so it will work
in my total's query? I've tried several different ways, but can't get it to
work.

Thanks!
Alan B. Densky
 
Hi,


Try changing

SUM(iif( ... , 1, 0))

to


Nz( SUM( iif( ..., 1, NULL )), 1)




Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel!!! That's got it perfect!

Alan


Michel Walsh said:
Hi,


Try changing

SUM(iif( ... , 1, 0))

to


Nz( SUM( iif( ..., 1, NULL )), 1)




Hoping it may help,
Vanderghast, Access MVP



Alan B. Densky said:
Hello,

This is a calculated field in my totals query:

WriteOffPerUnit: CCur(Nz(Sum([ReservesLoss])/(Sum(IIf([EndType]="CH" Or
[EndType]="NC" Or [EndType]="IN",1,0))),0))

The query works correctly except:
My problem is that if the record source HAS a record with an EndType of
"PO", but it DOESN'T have a record with an end type of "CH", "IN", or "NC",
the divisor for the Sum([ReserveLoss]) ends up being zero. And as we all
know, that's a no-no. I need the answer in this case to be zero.

Can somebody please help me re-write this calculated field so it will work
in my total's query? I've tried several different ways, but can't get it to
work.

Thanks!
Alan B. Densky
Click to expand...
Click to expand...
 
Back
Top