Replace the "Null" with "0" in:
FROM:
=IIf(Sum(Abs([Payment Type]="Lease")) = 0 , Null , Sum(Abs([Payment
Type]="Lease") * ([Warranty Income]+[Finance Reserve]+[Credit Life
Income]+[Disability Income]+[GAP Income]))/Sum(Abs([Payment Type]="Lease")))
TO:
=IIf(Sum(Abs([Payment Type]="Lease")) = 0 , 0 , Sum(Abs([Payment
Type]="Lease") * ([Warranty Income]+[Finance Reserve]+[Credit Life
Income]+[Disability Income]+[GAP Income]))/Sum(Abs([Payment Type]="Lease")))
--
Duane Hookom
Microsoft Access MVP
Reese said:
I need some assistance. I don't know how to make it show "0" if there are no
leases in the set of records.
Duane Hookom said:
Did you make the adjustment to the expression or do you need some assistance?
--
Duane Hookom
Microsoft Access MVP
:
Thank you, that seems to work. The only small adjustment I need to it is
that, if there are no leases in the field [Payment Type] for that subgroup
then nothing appears in that footer. I need a "zero" to appear.
:
Try:
=IIf(Sum(Abs([Payment Type]="Lease")) = 0 , Null, Sum(Abs([Payment
Type]="Lease") * ([Warranty Income]+[Finance Reserve]+[Credit Life
Income]+[Disability Income]+[GAP Income]))/Sum(Abs([Payment Type]="Lease")))
--
Duane Hookom
Microsoft Access MVP
:
I'm using it in the section footer and report footer on a report.
:
Where are you using the expression?
--
Duane Hookom
Microsoft Access MVP
:
I tried that expression and I'm getting the error message "Cannot have
aggregate function in the expression" ...
:
Try something like:
=IIf(Sum(Abs([Payment Type]="Lease")) = 0 , Null, Abs(Sum(Abs([Payment
Type]="Lease") *([Warranty Income]+[Finance Reserve]+[Credit Life
Income]+[Disability Income]+[GAP Income])/Sum(Abs([Payment Type]="Lease"))))
--
Duane Hookom
Microsoft Access MVP
:
I'm trying to write an expression that adds the values in multiple fields if
one of the fields meets certain criteria and then divides by that number.
Here's the expression I'm using, but it's not functioning properly.
=Abs(Sum(IIf([Payment Type]="Lease",[Warranty Income]+[Finance
Reserve]+[Credit Life Income]+[Disability Income]+[GAP
Income],0))/Sum(Nz([Payment Type]="Lease",0)))
The problem I cannot seem to overcome is that when there are no records in
the group where the field [Payment Type]='Lease' I get a value of #Num!
instead of zero. I thought I had that beat with the Nz, but I guess not.
