Fix Access DSum Errors When Query Uses Date Parameters
Question details
The user experiences calculation errors in Microsoft Access reports using DSum expressions when date parameters are added to the underlying query.

- 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.
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.
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.
Navigate to the Create tab on the Access Ribbon and click Blank Form to start a new unbound form.
Insert two text boxes into the form. Name them 'txtFrom' and 'txtTo' to represent your start and end dates.
Open your underlying query in Design View. Replace the existing criteria 'Between [From] And [To]' with 'BETWEEN Forms!YourFormName!txtFrom AND Forms!YourFormName!txtTo'.
Place a command button on your form and configure its On Click event to open the report.
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.
Declare Query Parameters as DATETIME
Explicitly define your parameters as DATETIME in the query to prevent Access from misinterpreting regional date formats.
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.

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.




