Sum a field based on a condition in another

[PizzaBoy]

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which produces a
conditional sum in a group footer (grouped by User). For example, I would
like the text box to sum the total of all agreements for that user where the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only inconsistent and
incorrect totals or none at all.

Any help is appreciated.

 

[Ken Snell [MVP]]

Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

 

[PizzaBoy]

Thanks for the suggestion. I actually tried that right after the post but to
no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P" and there
are the appropriated amounts in the amount field yet several of the users
show no total in the text box-only a zero. What am I doing wrong? Thanks.

Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which produces a
conditional sum in a group footer (grouped by User). For example, I would
like the text box to sum the total of all agreements for that user where the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only inconsistent and
incorrect totals or none at all.

Any help is appreciated.

 

[Ken Snell [MVP]]

Is the AGMTTYPE field bound to a control in the report? If not, you need to
bind it to a control (even if the control is invisible) so that the report
can "see" it.

I'm not able to see your data nor results, so if the above doesn't work,
then post some examples of what you're seeing and what your data are.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion. I actually tried that right after the post but to
no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P" and there
are the appropriated amounts in the amount field yet several of the users
show no total in the text box-only a zero. What am I doing wrong? Thanks.

Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which

produces
a

conditional sum in a group footer (grouped by User). For example, I would
like the text box to sum the total of all agreements for that user

where
the

agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only

inconsistent
and

incorrect totals or none at all.

Any help is appreciated.

 

[PizzaBoy]

Thanks again for the information. The same thought had occurred to me and I
added the fields of [AGMTTYPE] and [AGMTAMT] to the detail section of the
group and set their property to invisible but I still have the same result.

I have the report grouped by the [USER]

in the group header I have placed the labels. in the group footer I have
placed the text box named [TOTAL]=IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). Have
since added the fields [AGMTTYPE] and [AGMTAMT] to the detail section but
kept them hidden. I still end up with the same result of an incorrect
sum amount or just a 0 when there are "P" type agreements.

I am stuck. Thanks for the help.

Is the AGMTTYPE field bound to a control in the report? If not, you need
to
bind it to a control (even if the control is invisible) so that the report
can "see" it.

I'm not able to see your data nor results, so if the above doesn't work,
then post some examples of what you're seeing and what your data are.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion. I actually tried that right after the post but to
no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P" and
there
are the appropriated amounts in the amount field yet several of the users
show no total in the text box-only a zero. What am I doing wrong? Thanks.

Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which produces
a
conditional sum in a group footer (grouped by User). For example, I would
like the text box to sum the total of all agreements for that user where
the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only inconsistent
and
incorrect totals or none at all.

Any help is appreciated.

 

[Ken Snell [MVP]]

OK - let's define what you mean by incorrect sum amount or just a zero.

Post some examples of the data that you're using, what the sum should be,
and what the sum actually is on the report.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for the information. The same thought had occurred to me and I
added the fields of [AGMTTYPE] and [AGMTAMT] to the detail section of the
group and set their property to invisible but I still have the same result.

I have the report grouped by the [USER]

in the group header I have placed the labels. in the group footer I have
placed the text box named [TOTAL]=IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). Have
since added the fields [AGMTTYPE] and [AGMTAMT] to the detail section but
kept them hidden. I still end up with the same result of an incorrect
sum amount or just a 0 when there are "P" type agreements.

I am stuck. Thanks for the help.

Is the AGMTTYPE field bound to a control in the report? If not, you need
to
bind it to a control (even if the control is invisible) so that the report
can "see" it.

I'm not able to see your data nor results, so if the above doesn't work,
then post some examples of what you're seeing and what your data are.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion. I actually tried that right after the post

but
to

no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P" and
there
are the appropriated amounts in the amount field yet several of the users
show no total in the text box-only a zero. What am I doing wrong? Thanks.


Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which produces
a
conditional sum in a group footer (grouped by User). For example, I would
like the text box to sum the total of all agreements for that user where
the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only inconsistent
and
incorrect totals or none at all.

Any help is appreciated.

 

[PizzaBoy]

In the detail section I have added the [AGMTTYPE] and [AGMTAMT] fields and
made them visible so I can check the data. In the group footer I have the
text box IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). When I run the report: USER
11 has a record where [AGMTTYPE]="P" and the [AGMTAMT]=60 yet the text box
in the group footer still shows "0" when it should show 60. Do I have this
set up wrong? Am I exceeding the capability of IIf? Is there another way to
have a report group footer sum a value for all records that meet a criteria
in a group?

Thanks for the help.

OK - let's define what you mean by incorrect sum amount or just a zero.

Post some examples of the data that you're using, what the sum should be,
and what the sum actually is on the report.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for the information. The same thought had occurred to me and I
added the fields of [AGMTTYPE] and [AGMTAMT] to the detail section of the
group and set their property to invisible but I still have the same result.

I have the report grouped by the [USER]

in the group header I have placed the labels. in the group footer I have
placed the text box named [TOTAL]=IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). Have
since added the fields [AGMTTYPE] and [AGMTAMT] to the detail section but
kept them hidden. I still end up with the same result of an incorrect
sum amount or just a 0 when there are "P" type agreements.

I am stuck. Thanks for the help.

Is the AGMTTYPE field bound to a control in the report? If not, you
need
to
bind it to a control (even if the control is invisible) so that the report
can "see" it.

I'm not able to see your data nor results, so if the above doesn't
work,
then post some examples of what you're seeing and what your data are.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion. I actually tried that right after the post but
to
no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P" and
there
are the appropriated amounts in the amount field yet several of the users
show no total in the text box-only a zero. What am I doing wrong? Thanks.


Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which
produces
a
conditional sum in a group footer (grouped by User). For example, I
would
like the text box to sum the total of all agreements for that user
where
the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only
inconsistent
and
incorrect totals or none at all.

Any help is appreciated.

 

[Ken Snell [MVP]]

Try it again with the expression that I posted:

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)


--

Ken Snell
<MS ACCESS MVP>

In the detail section I have added the [AGMTTYPE] and [AGMTAMT] fields and
made them visible so I can check the data. In the group footer I have the
text box IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). When I run the report: USER
11 has a record where [AGMTTYPE]="P" and the [AGMTAMT]=60 yet the text box
in the group footer still shows "0" when it should show 60. Do I have this
set up wrong? Am I exceeding the capability of IIf? Is there another way to
have a report group footer sum a value for all records that meet a criteria
in a group?

Thanks for the help.

OK - let's define what you mean by incorrect sum amount or just a zero.

Post some examples of the data that you're using, what the sum should be,
and what the sum actually is on the report.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for the information. The same thought had occurred to me

and
I

added the fields of [AGMTTYPE] and [AGMTAMT] to the detail section of the
group and set their property to invisible but I still have the same result.

I have the report grouped by the [USER]

in the group header I have placed the labels. in the group footer I have
placed the text box named [TOTAL]=IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). Have
since added the fields [AGMTTYPE] and [AGMTAMT] to the detail section but
kept them hidden. I still end up with the same result of an incorrect
sum amount or just a 0 when there are "P" type agreements.

I am stuck. Thanks for the help.


Is the AGMTTYPE field bound to a control in the report? If not, you
need
to
bind it to a control (even if the control is invisible) so that the report
can "see" it.

I'm not able to see your data nor results, so if the above doesn't
work,
then post some examples of what you're seeing and what your data are.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion. I actually tried that right after the

post
but

to
no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P" and
there
are the appropriated amounts in the amount field yet several of the users
show no total in the text box-only a zero. What am I doing wrong? Thanks.


Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which
produces
a
conditional sum in a group footer (grouped by User). For example, I
would
like the text box to sum the total of all agreements for that user
where
the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only
inconsistent
and
incorrect totals or none at all.

Any help is appreciated.

 

[PizzaBoy]

That worked beautifully this time, must have been my mistake last time. I
also figured another way to do it-I created an expression in the underlying
query that did the same thing and then added a sum field for the expression
result in the report. Thanks for all the help.

Try it again with the expression that I posted:

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)


--

Ken Snell
<MS ACCESS MVP>

In the detail section I have added the [AGMTTYPE] and [AGMTAMT] fields
and
made them visible so I can check the data. In the group footer I have the
text box IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0). When I run the report:
USER
11 has a record where [AGMTTYPE]="P" and the [AGMTAMT]=60 yet the text
box
in the group footer still shows "0" when it should show 60. Do I have this
set up wrong? Am I exceeding the capability of IIf? Is there another way to
have a report group footer sum a value for all records that meet a criteria
in a group?

Thanks for the help.

OK - let's define what you mean by incorrect sum amount or just a zero.

Post some examples of the data that you're using, what the sum should be,
and what the sum actually is on the report.

--

Ken Snell
<MS ACCESS MVP>

Thanks again for the information. The same thought had occurred to me and
I
added the fields of [AGMTTYPE] and [AGMTAMT] to the detail section of the
group and set their property to invisible but I still have the same
result.

I have the report grouped by the [USER]

in the group header I have placed the labels. in the group footer I have
placed the text box named
[TOTAL]=IIf([AGMTTYPE]='P',SUM[(AGMTAMT]),0).
Have
since added the fields [AGMTTYPE] and [AGMTAMT] to the detail section but
kept them hidden. I still end up with the same result of an incorrect
sum amount or just a 0 when there are "P" type agreements.

I am stuck. Thanks for the help.


Is the AGMTTYPE field bound to a control in the report? If not, you
need
to
bind it to a control (even if the control is invisible) so that the
report
can "see" it.

I'm not able to see your data nor results, so if the above doesn't
work,
then post some examples of what you're seeing and what your data
are.

--

Ken Snell
<MS ACCESS MVP>

Thanks for the suggestion. I actually tried that right after the post
but
to
no avail. I temporarily added the Type and Amount fields in the detail
section so I could visually verify there are types listed as "P"
and
there
are the appropriated amounts in the amount field yet several of the
users
show no total in the text box-only a zero. What am I doing wrong?
Thanks.


Try this. It will give a zero as the result if nothing matches.

=Sum(IIf([AGMTTYPE]="P", [AGMTAMT], 0)

--

Ken Snell
<MS ACCESS MVP>

Hello Group. I have report based on three fields. Agreement Amount,
Agreement Type, and User. I am trying to create a text box which
produces
a
conditional sum in a group footer (grouped by User). For
example, I
would
like the text box to sum the total of all agreements for that user
where
the
agreement type is "P". I am currently using an IIf statement
(IIF([AGMTTYPE]='P',SUM([AGMTAMT]),NULL). So far I get only
inconsistent
and
incorrect totals or none at all.

Any help is appreciated.