Filtering Subtotals from a report

[debinnyc]

Working in Access 2003, have a table with customer information and charitable
donation amounts. I wrote a query to pull by a date range, and ran a report
to parse out each customer by their account number, address, donation amount,
etc. I have subtotals in the report for each customer and a grand total at
the end. My end game is to send letters via mail merge from a list of
customers that have donated above a certain amount (as a total for that
customer). For example, I want to return only customers that have donated
over $100 total, and have that information in some type of format to use as a
basis for mail merge. I know this question also involves word, but if you
could help me with the access side it would be greatly appreciated.

 

[Allen Browne]

If the report calculates the total as it goes and comes up with the final
figure per donor, then it does not have the final figure before it starts,
and so you can't filter the report based on the total that has not been
calculated yet.

This means you will have to provide it with some way to calculate the total
per donor before the report runs. One way to do this is to use a subquery in
the WhereCondition of OpenReport. You will need some understanding of VBA
and some awareness of SQL to achieve this.

This example assumes your main report is bound to tblDonor, and you you want
to filter it to those who donated at least 100 in the period between
StartDate and EndDate boxes on your form.

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsDate(Me.StartDate) AND IsDate(Me.EndDate) Then
strWhere = "(SELECT Sum(Amount) AS HowMuch " & _
"FROM tblDonation AS D " & _
"WHERE D.DonorID = tblDonor.DonorID " & _
"AND (D.DonationDate >= " & Format(Me.StartDate, strcJetDate) & _
") AND (D.DonationDate < " & Format(Me.EndDate + 1, strcJetDate) & _
")) > 100"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
Else
MsgBox "Both dates required.
End If

If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html

If this kind of code is new, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html