IIF summing in report

  • Thread starter Thread starter Daniel Collison
  • Start date Start date
D

Daniel Collison

In the detail section of a report, the following fields are included:

Procedure Code (Proc Code)
Claim Status (Claim Status)
Service Units (SumOfUnits_allowed)

A procedure code can have a claim status of 1 (allowed) or 2 (not allowed).
The service units represent a total of all services -- as expressed in units
-- associated with a procedure code of 1 or 2.

In a group footer, I have included a calculated text box to sum all service
units associated with procedure codes in the group. The text box control
source property is set as =Sum([SumOfunits_allowed]). This text box does not
distinguish between service units associated with claim statuses of 1 or 2.

What I really need is to sum for claim status 1 and 2. I created two
separate text boxes with the following formulas in the control source:
=IIf([Claim Status]=1,Sum([SumOfUnits_allowed]),"") and =IIf([Claim
Status]=2,Sum([SumOfUnits_allowed]),""). Neither text box returns a result.

I have limited MS Access experience, and would appreciate any advice related
to this matter?
 
In the detail section of a report, the following fields are included:

Procedure Code (Proc Code)
Claim Status (Claim Status)
Service Units (SumOfUnits_allowed)

A procedure code can have a claim status of 1 (allowed) or 2 (not allowed).
The service units represent a total of all services -- as expressed in units
-- associated with a procedure code of 1 or 2.

In a group footer, I have included a calculated text box to sum all service
units associated with procedure codes in the group. The text box control
source property is set as =Sum([SumOfunits_allowed]). This text box does not
distinguish between service units associated with claim statuses of 1 or 2.

What I really need is to sum for claim status 1 and 2. I created two
separate text boxes with the following formulas in the control source:
=IIf([Claim Status]=1,Sum([SumOfUnits_allowed]),"") and =IIf([Claim
Status]=2,Sum([SumOfUnits_allowed]),""). Neither text box returns a result.
Click to expand...


=Sum(IIf([Claim Status]=1,[SumOfUnits_allowed],0))
=Sum(IIf([Claim Status]=2,[SumOfUnits_allowed],0))
 
Marshall:

Thanks for you quick response. I used the code you recommended for the two
text boxes. Now I get the following error message when running the report:
"Data type mismatch in criteria expression." All fields in questions have a
"standard" format.

Any thoughts?

Thanks,

Daniel

Marshall Barton said:
In the detail section of a report, the following fields are included:

Procedure Code (Proc Code)
Claim Status (Claim Status)
Service Units (SumOfUnits_allowed)

A procedure code can have a claim status of 1 (allowed) or 2 (not allowed).
The service units represent a total of all services -- as expressed in units
-- associated with a procedure code of 1 or 2.

In a group footer, I have included a calculated text box to sum all service
units associated with procedure codes in the group. The text box control
source property is set as =Sum([SumOfunits_allowed]). This text box does not
distinguish between service units associated with claim statuses of 1 or 2.

What I really need is to sum for claim status 1 and 2. I created two
separate text boxes with the following formulas in the control source:
=IIf([Claim Status]=1,Sum([SumOfUnits_allowed]),"") and =IIf([Claim
Status]=2,Sum([SumOfUnits_allowed]),""). Neither text box returns a result.
Click to expand...


=Sum(IIf([Claim Status]=1,[SumOfUnits_allowed],0))
=Sum(IIf([Claim Status]=2,[SumOfUnits_allowed],0))
Click to expand...
 
Double check that [Claim Status] abd [SumOfUnits_allowed]
are Number type fields in the report's record source
table/query (not the names of controls in the report).

If the report's record source is a Totals type query that
calculates [SumOfUnits_allowed] in an aggregate function,
this probably won't work.

Note that the Format of a field is irrelevant.

Note that this is one of those situations where the
distinction between the words Field and Control can be
critical so choose your words carefully.
--
Marsh
MVP [MS Access]


Daniel said:
Thanks for you quick response. I used the code you recommended for the two
text boxes. Now I get the following error message when running the report:
"Data type mismatch in criteria expression." All fields in questions have a
"standard" format.


Marshall Barton said:
Daniel said:
In the detail section of a report, the following fields are included:

Procedure Code (Proc Code)
Claim Status (Claim Status)
Service Units (SumOfUnits_allowed)

A procedure code can have a claim status of 1 (allowed) or 2 (not allowed).
The service units represent a total of all services -- as expressed in units
-- associated with a procedure code of 1 or 2.

In a group footer, I have included a calculated text box to sum all service
units associated with procedure codes in the group. The text box control
source property is set as =Sum([SumOfunits_allowed]). This text box does not
distinguish between service units associated with claim statuses of 1 or 2.

What I really need is to sum for claim status 1 and 2. I created two
separate text boxes with the following formulas in the control source:
=IIf([Claim Status]=1,Sum([SumOfUnits_allowed]),"") and =IIf([Claim
Status]=2,Sum([SumOfUnits_allowed]),""). Neither text box returns a result.
Click to expand...


=Sum(IIf([Claim Status]=1,[SumOfUnits_allowed],0))
=Sum(IIf([Claim Status]=2,[SumOfUnits_allowed],0))
Click to expand...
Click to expand...
 
Marshall:

Thanks again...your advice was especially helpful. The [Claim Status] field
was a text field. I was able to change the field to a number field and the
problem was solved!

Again...I appreciate your assistance!

Daniel

Marshall Barton said:
Double check that [Claim Status] abd [SumOfUnits_allowed]
are Number type fields in the report's record source
table/query (not the names of controls in the report).

If the report's record source is a Totals type query that
calculates [SumOfUnits_allowed] in an aggregate function,
this probably won't work.

Note that the Format of a field is irrelevant.

Note that this is one of those situations where the
distinction between the words Field and Control can be
critical so choose your words carefully.
--
Marsh
MVP [MS Access]


Daniel said:
Thanks for you quick response. I used the code you recommended for the two
text boxes. Now I get the following error message when running the report:
"Data type mismatch in criteria expression." All fields in questions have a
"standard" format.


Marshall Barton said:
Daniel Collison wrote:

In the detail section of a report, the following fields are included:

Procedure Code (Proc Code)
Claim Status (Claim Status)
Service Units (SumOfUnits_allowed)

A procedure code can have a claim status of 1 (allowed) or 2 (not allowed).
The service units represent a total of all services -- as expressed in units
-- associated with a procedure code of 1 or 2.

In a group footer, I have included a calculated text box to sum all service
units associated with procedure codes in the group. The text box control
source property is set as =Sum([SumOfunits_allowed]). This text box does not
distinguish between service units associated with claim statuses of 1 or 2.

What I really need is to sum for claim status 1 and 2. I created two
separate text boxes with the following formulas in the control source:
=IIf([Claim Status]=1,Sum([SumOfUnits_allowed]),"") and =IIf([Claim
Status]=2,Sum([SumOfUnits_allowed]),""). Neither text box returns a result.


=Sum(IIf([Claim Status]=1,[SumOfUnits_allowed],0))
=Sum(IIf([Claim Status]=2,[SumOfUnits_allowed],0))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top