Others

How to Use DSum for a Date- and ID-Based Running Total in Access

John WilsonJohn Wilson Nov 4, 2026 870 views

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.

How to Use DSum for a Date- and ID-Based Running Total in Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Format the date criteria

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").

2
Isolate the current record's values

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.

3
Apply the dual-condition logic

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.

4
Write the final DSum expression

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] & ")")

Build Explicit DSum Criteria Using Correct Formatting and Aliasing
Performance Consideration: Using complex DSum functions in large datasets can slow down query performance. If the query runs too slowly, consider writing a VBA function or using a temporary table.
Free Microsoft Office alternative

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. 1. Open your data in WPS Spreadsheet: Export your table data to an Excel format and open it easily using WPS Spreadsheet.
  2. 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. 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.
Fully compatible with Microsoft Excel formats (.xlsx, .xls) and features.Calculate running totals instantly using simple formulas without complex criteria syntax.Lightweight and runs smoothly on Windows, Mac, Linux, iOS, and Android.Free to use with a familiar, easy-to-navigate user interface.
QA img-9

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.