Conditional Calculations in Access 2002 reports

[Guest]

I have built a database and am generating reports. I have a fields in the
data input tables that indicate the amount of a charge from a service
provider, and another that indicates if the charge has been paid
(default=No). I want to generate totals in the footer of a report that will
toata (a) Total Paid, and (b) Total Unpaid. These totals will , in effect, be
Sum[fee]when([paid=Yes]), and Sum[fee]when([paid]=No. Needless to say, these
expressiond don't work. What will?

 

[Peter R. Fletcher]

Create two invisible (Visible = No) controls in the Detail section of
your Report. Call them something sensible like RunSumPaid and
RunSumUnpaid. Set their Running Sum Properties to Yes.
Set the Control Source of RunSumPaid to '=-([paid]*[fee])' and the
Control Source of RunSumUnpaid to '=-(Not([paid])*[fee])'.

In the footer of the Report, create two visible fields for your
totals, and set their Control Sources respectively to '=RunSumPaid'
and '=RunSumUnpaid'. You're done!

This relies on the fact that, in a numeric context, the logical value
True is interpreted as -1 and the logical value False is interpreted
as zero.

I have built a database and am generating reports. I have a fields in the
data input tables that indicate the amount of a charge from a service
provider, and another that indicates if the charge has been paid
(default=No). I want to generate totals in the footer of a report that will
toata (a) Total Paid, and (b) Total Unpaid. These totals will , in effect, be
Sum[fee]when([paid=Yes]), and Sum[fee]when([paid]=No. Needless to say, these
expressiond don't work. What will?

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Duane Hookom]

Unless I missed something another method might be to add two text boxes with
control sources of:
= Sum( Abs( [Paid]=True ) * [Fee] )
and
= Sum( Abs( [Paid]=False ) * [Fee] )
--
Duane Hookom
MS Access MVP

Create two invisible (Visible = No) controls in the Detail section of
your Report. Call them something sensible like RunSumPaid and
RunSumUnpaid. Set their Running Sum Properties to Yes.
Set the Control Source of RunSumPaid to '=-([paid]*[fee])' and the
Control Source of RunSumUnpaid to '=-(Not([paid])*[fee])'.

In the footer of the Report, create two visible fields for your
totals, and set their Control Sources respectively to '=RunSumPaid'
and '=RunSumUnpaid'. You're done!

This relies on the fact that, in a numeric context, the logical value
True is interpreted as -1 and the logical value False is interpreted
as zero.

I have built a database and am generating reports. I have a fields in the
data input tables that indicate the amount of a charge from a service
provider, and another that indicates if the charge has been paid
(default=No). I want to generate totals in the footer of a report that
will
toata (a) Total Paid, and (b) Total Unpaid. These totals will , in effect,
be
Sum[fee]when([paid=Yes]), and Sum[fee]when([paid]=No. Needless to say,
these
expressiond don't work. What will?

Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher

 

[PC Datasheet]

Put the following in the Total Paid textbox's controlsource:
=DSum("[Fee]","NameOfReportRecordsource","[Paid] = True")

Put the following in the Total Unpaid textbox's controlsource:
=DSum("[Fee]","NameOfReportRecordsource","[Paid] = False")