How to Use DSum for a Date- and ID-Based Running Total in Access
Question details
Calculate a cumulative running total in an editable MS Access query based on records sorted by a date field and a secondary numeric ID.

- Product
- MS Access
- Device & OS
- not provided
- Scenario
- Creating an editable query where numerical values accumulate row by row based on chronological order and a unique identifier to break ties.
- Observed behavior
- Simple date comparisons in DSum criteria often fail due to formatting issues or ambiguous field references, resulting in errors or zero values.
Ensure your table has a specific date field and a unique sequential numeric ID field, as both are required to correctly order the running total and break any ties on identical dates.
Build Explicit DSum Criteria Using Correct Formatting and Aliasing
Use proper string concatenation and date formatting within the DSum expression so Access can distinguish between the current record's values and the aggregated dataset.
A common mistake when building a DSum expression is writing criteria like '[CloseDate] < [CloseDate]'. Access cannot evaluate this because it cannot distinguish between the field in the queried table and the current row's field.
By dynamically injecting the current row's values into a criteria string and formatting dates universally, you can create a reliable running total.
Dates in Access expressions can be misinterpreted depending on your regional settings. Wrap your date field in a format function to enforce a universal standard, such as Format([CloseDate], "yyyy-mm-dd").
Instead of referencing the field directly inside the quotes, concatenate the current record's value outside the quotes using the '&' operator. Ensure date values are wrapped in '#' characters.
Your criteria must include two parts to handle ties: records with an earlier date, OR records with the same date but an ID less than or equal to the current ID.
Create a new column in your query design grid. Enter an expression similar to: RunningTotal: DSum("[Amount]", "YourTable", "[CloseDate] < #" & Format([CloseDate], "yyyy-mm-dd") & "# OR ([CloseDate] = #" & Format([CloseDate], "yyyy-mm-dd") & "# AND [ID] <= " & [ID] & ")")

Looking for an Easier Way to Calculate Running Totals?
While Microsoft Access relies on complex functions like DSum for cumulative calculations, spreadsheet software can handle these tasks effortlessly. WPS Office offers a powerful, free Spreadsheet tool that makes data calculations simple, visual, and highly compatible with Microsoft Excel formats.
- 1. Open your data in WPS Spreadsheet: Export your table data to an Excel format and open it easily using WPS Spreadsheet.
- 2. Sort the data: Use the Sort feature under the Data tab to order your rows chronologically by Date, and then by your secondary ID.
- 3. Apply a simple SUM formula: In the adjacent column for the first record (e.g., C2), enter =SUM($B$2:B2) and drag the fill handle down to calculate the running total instantly.

Frequently Asked Questions
Why does my DSum expression return an error when comparing dates?
This happens when the criteria attempt a mathematical comparison on unformatted date text. You must explicitly format the date using the Format() function and enclose the literal date values in '#' characters within the string.
How can a field like [CloseDate] < [CloseDate] ever be true?
It cannot. If you write it this way, Access sees it as a tautological contradiction. You must use concatenation to insert the current record's value as a static string into the criteria argument, e.g., "[CloseDate] < #" & [CloseDate] & "#".
Can I calculate running totals without using DSum?
Yes, but usually not in an editable query. You can generate running totals on Access Reports using the 'Running Sum' property, or you can export the data to spreadsheet software where simple relative-reference formulas handle the cumulative math.




