Conditional Expressions

R

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.
 
D

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

Reese

I tried that expression and I'm getting the error message "Cannot have
aggregate function in the expression" ...

Duane Hookom said:
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


Reese said:
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.
 
D

Duane Hookom

Where are you using the expression?

--
Duane Hookom
Microsoft Access MVP


Reese said:
I tried that expression and I'm getting the error message "Cannot have
aggregate function in the expression" ...

Duane Hookom said:
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


Reese said:
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.
 
R

Reese

I'm using it in the section footer and report footer on a report.

Duane Hookom said:
Where are you using the expression?

--
Duane Hookom
Microsoft Access MVP


Reese said:
I tried that expression and I'm getting the error message "Cannot have
aggregate function in the expression" ...

Duane Hookom said:
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.
 
D

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


Reese said:
I'm using it in the section footer and report footer on a report.

Duane Hookom said:
Where are you using the expression?

--
Duane Hookom
Microsoft Access MVP


Reese said:
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.
 
R

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.

Duane Hookom said:
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


Reese said:
I'm using it in the section footer and report footer on a report.

Duane Hookom said:
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.
 
D

Duane Hookom

Did you make the adjustment to the expression or do you need some assistance?

--
Duane Hookom
Microsoft Access MVP


Reese said:
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.

Duane Hookom said:
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


Reese said:
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.
 
R

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.

Duane Hookom said:
Did you make the adjustment to the expression or do you need some assistance?

--
Duane Hookom
Microsoft Access MVP


Reese said:
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.

Duane Hookom said:
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.
 
D

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


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


Reese said:
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.
 
R

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.

Duane Hookom said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top