Totals Query And Division By Zero

[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

 

[Michel Walsh]

Hi,


Try changing

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

to


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




Hoping it may help,
Vanderghast, Access MVP

 

[Alan B. Densky]

Thanks Michel!!! That's got it perfect!

Alan

Hi,


Try changing

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

to


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




Hoping it may help,
Vanderghast, Access MVP

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