Conditional Expressions

[Reese]

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.

 

[Duane Hookom]

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"))))

 

[Reese]

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.

 

[Duane Hookom]

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.

 

[Reese]

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.

 

[Duane Hookom]

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.

 

[Reese]

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.

 

[Duane Hookom]

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.

 

[Reese]

I need some assistance. I don't know how to make it show "0" if there are no
leases in the set of records.

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.

 

[Duane Hookom]

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

I need some assistance. I don't know how to make it show "0" if there are no
leases in the set of records.

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.

 

[Reese]

That did the trick. I really appreciate your help. Everytime you help me
work out a problem I learn a little more about this very complicated program.
It's great! Thank you very much.

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

I need some assistance. I don't know how to make it show "0" if there are no
leases in the set of records.

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.