Others

Sum Microsoft Access Report Values for a Date Range or Quarter

Adam DavisAdam Davis Nov 14, 2026 868 views

Question details

Add a total for funding values within a specified date range to an Access report, with the option to total other quarters.

Sum Microsoft Access Report Values for a Date Range or Quarter
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the report in Design View

In the Navigation Pane, right-click the report and select Design View.

2
Add a total text box

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.

3
Enter the conditional expression

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.

4
Preview the total

Switch to Report View or Print Preview and check the new total against the records in the requested date range.

Add a conditional date-range total to the report
This range is not a calendar quarter: September 1 through December 31 covers four months. For calendar Q4 2019, change the first DateSerial value to DateSerial(2019,10,1). The exclusive January 1 end date also includes December 31 records with a time value.
Free Microsoft Office alternative

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.

Review exported funding values and build spreadsheet summaries by date.Open and work with Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for an easier move from Microsoft Office.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.