Sum Microsoft Access Report Values for a Date Range or Quarter
Question details
Add a total for funding values within a specified date range to an Access report, with the option to total other quarters.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An existing report sums the Billed Funding Source field and needs a separate total for records dated September 1 through December 31, 2019.
- Observed behavior
- The existing Sum expression totals funding values without a date condition.
Confirm that the report's record source includes both the date field and the numeric Billed Funding Source field. Check the exact field names and make sure the report's filters do not exclude records you want to total.
Add a conditional date-range total to the report
Use an unbound text box to show the September through December total alongside the existing overall total.
In the Navigation Pane, right-click the report and select Design View.
On the Design tab, select Text Box and place an unbound text box in the Report Footer. Keep the existing =Sum([Billed Funding Source]) text box if you still need the overall total.
Select the new text box, open its Property Sheet, and set Control Source to =Sum(IIf([Your Date Field]>=DateSerial(2019,9,1) And [Your Date Field]<DateSerial(2020,1,1),Nz([Billed Funding Source],0),0)). Replace Your Date Field and Billed Funding Source with the actual field names.
Switch to Report View or Print Preview and check the new total against the records in the requested date range.

Create a query that totals funding by calendar quarter
Use a grouped query when you need a separate total for each year and quarter.
Review exported funding totals in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface for working with exported report data. Open compatible Excel files in WPS Spreadsheets to review funding values and create date-based summaries. WPS Office does not modify Access reports or replace an Access database.

Frequently Asked Questions
Why doesn't Q4 include September?
Calendar Q4 runs from October 1 through December 31. To include September as well, use a custom date-range expression starting at DateSerial(2019,9,1).
Where should I put the conditional total in my report?
Open the report in Design View and place an unbound text box in the Report Footer for a report-wide total. Its calculation is limited to records included in the report's record source and filters.
How can I total a different quarter?
In the text box's Control Source, change the DateSerial start date to the first day of the quarter and the exclusive end date to the first day of the next quarter. For recurring totals across many quarters, use a grouped query with Year and DatePart('q',...).
Can a separate summary report show totals from several subreports?
A main report can reference a total control in a subreport it contains, using a reference such as =[SubreportControlName].Report![txtTotal]. For a separate summary report, consider basing its totals on shared aggregate queries or adding the relevant subreports to it.




