Others

Fix Access DSum Errors When Query Uses Date Parameters

Steve KSteve K Nov 2, 2026 868 views

Question details

The user experiences calculation errors in Microsoft Access reports using DSum expressions when date parameters are added to the underlying query.

Fix Access DSum Errors When Query Uses Date Parameters
Product
Microsoft Access
Device & OS
not provided
Scenario
Generating a report involving income, expenses, and balance calculations based on specified date ranges.
Observed behavior
Adding 'Between [From] And [To]' criteria causes the DSum function to return errors instead of calculating the report totals.
Before you start

Ensure you have a backup of your Access database before modifying queries or creating new unbound forms, as syntax errors can temporarily break reporting functionality.

Solution 1Recommended

Use an Unbound Form for Parameter Prompts

Resolve DSum errors by referencing date parameter controls on an open unbound form rather than using unresolved system-generated prompts.

The DSum function cannot process queries that contain unresolved parameter prompts, such as simple [From] and [To] brackets. To fix this, you need to collect these parameters using an unbound dialog form and reference the form's controls directly within your query.

1
Create an unbound form

Navigate to the Create tab on the Access Ribbon and click Blank Form to start a new unbound form.

2
Add text box controls

Insert two text boxes into the form. Name them 'txtFrom' and 'txtTo' to represent your start and end dates.

3
Update the query criteria

Open your underlying query in Design View. Replace the existing criteria 'Between [From] And [To]' with 'BETWEEN Forms!YourFormName!txtFrom AND Forms!YourFormName!txtTo'.

4
Add an execution button

Place a command button on your form and configure its On Click event to open the report.

5
Keep the form open

Ensure the unbound form remains open while the report runs. If the form is closed, the DSum expression will not be able to read the date parameters and will return an error.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite Alternative?

While Microsoft Access is a powerful database tool, handling daily data analysis, reporting, and calculations is often faster and easier in a robust spreadsheet program. WPS Office offers a highly compatible, free, and lightweight alternative to Microsoft Office, perfect for managing your data without the complexities of SQL queries or database parameters.

Fully compatible with Microsoft Excel (.xlsx) formatsFeature-rich spreadsheet tool for complex formulas, pivot tables, and data analysisFree, lightweight, and fast-loading across Windows, Mac, and mobile devicesIntuitive interface with no steep learning curve for seamless migration
microsoft office alternative - wps office

Frequently Asked Questions

Why does DSum work without date parameters but fail when they are added?

DSum evaluates domain aggregates over a defined set of records in the background. When generic bracketed prompts (like [From]) are used, the DSum function cannot interactively ask the user for these parameters during calculation, which results in a continuous error loop.

Will unresolved parameters cause errors in other Access aggregate functions?

Yes. Just like DSum, other domain aggregate functions such as DCount, DAvg, and DMax will also fail to evaluate correctly if the underlying query relies on simple, unresolved system-generated parameter prompts.

Why do I need to explicitly declare DATETIME in Access queries?

Access may misinterpret ambiguous dates (like 08/09/2023) depending on local regional settings, confusing mm/dd/yyyy with dd/mm/yyyy. Declaring query parameters as DATETIME enforces strict date validation, ensuring accurate database queries and report generation.