Conditional Sums in Reports

[Amy E. Baggott]

I have a report that tracks all the payment records for the day. Some of the
records are for payments, and some are refunds. I have been summing them and
then having the sum put into an e-mail to send to my boss. Now he wants
separate totals for payments and refunds, with record counts for each. How
do I sum and count payments only if they are positive or only if they are
negative? If it were just on the report, I could probably do it by grouping
the payments and refunds, but I don't know how to get the group totals into
the e-mail as separate fields. Currently, I have the following code embedded
in an update procedure I run twice a day to synchronize this database with
another (non-Access) database.

' Run daily payment list
DoCmd.OpenReport "rptTodaysPayments", acViewPreview
Response = MsgBox("Send Report to sales guys?", vbYesNo + vbQuestion)
If Response = vbYes Then
DoCmd.SendObject acReport, "rptTodaysPayments",
"SnapshotFormat(*.snp)",
"(e-mail address removed);[email protected];[email protected];[email protected];[email protected];[email protected]",
"", "", "Today's IBS Payments", "Here are today's IBS Payments. We logged " &
Reports![rptTodaysPayments]![Text33] & " payments totaling " &
Format(Reports![rptTodaysPayments]![Text26], "$#,##0.00") & ".", True, ""
End If

The counts and totals have to be in the e-mail because if he is out of the
office, he opens it on his phone and can't open the snapshot of the report.
Any ideas?

 

[Ken Snell \(MVP\)]

You could add two fields to your report's RecordSource query that would sum
the payments and the refunds separately. You could do this with expressions
similar to this:

Sum(IIf([PaymentField]>0,[PaymentField]),0) AS PaymentSum

Sum(IIf([PaymentField]<0,[PaymentField]),0) AS RefundSum