Conditional Calculations in Access 2002 reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
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


Peter R. Fletcher said:
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?
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the
exchange.
Peter R. Fletcher
Click to expand...
 
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")
 
Back
Top