Others

Update Microsoft Access Query Dates via Form or Table

Emma Brown Oct 10, 2026 868 views

Question details

The user needs to dynamically update the date range (such as a fiscal year from December 1 to November 30) in a Microsoft Access query so users can modify it easily without accessing the query design.

How to Update Microsoft Access Query Dates Through a Form or Table

Product: Microsoft Access
Device & OS: not provided
Scenario: Calculating billable hours for a specific accounting year that changes annually.
Observed behavior: The user needs a safe method for end-users to update accounting year dates automatically or manually without exposing backend database objects or hard-coding criteria.

Before you start

Before modifying your database structure or query criteria, ensure you create a backup copy of your Microsoft Access file to prevent accidental data loss during the configuration process.

Solution 1Recommended

Create and Use a One-Row Settings Table

Store your accounting year start and end dates in a dedicated settings table. This allows users to update dates via a simple bound form without touching the query design.

By utilizing a one-row table without joins, Access will apply these settings universally across your query as a Cartesian product. Restricting the table to a single row guarantees data integrity.

Create a new settings table

Create a new table named 'tblSettings' with two Date/Time fields: 'StartDate' and 'EndDate'.

Enforce a single-record rule

Add a Primary Key field. In the field properties, set its default value to 1, and add a Validation Rule of '=1'. This prevents users from adding more than one record.

Input the current fiscal dates

Open the table in Datasheet View and enter the current accounting year dates (e.g., 12/1/2022 to 11/30/2023) into this single row.

Add the table to your query

Open your target calculation query in Design View, click 'Add Table', and include 'tblSettings'. Do not create any relationship lines (joins) to other tables.

Update the query criteria

In the query design grid, navigate to your transaction date field. Update the Criteria row to reference the settings table, for example: '>=[tblSettings].[StartDate] And <=[tblSettings].[EndDate]'.

Create a user form

Build a simple user form bound to 'tblSettings'. Users can now safely open this form to update the date range next year without seeing the query design.

Secure Access: This method effectively completely hides the query logic, protecting your database architecture from accidental modifications by everyday users.

Solution 2

Use a Custom VBA Function (AcctYear)

Create a custom VBA function to dynamically calculate and compare the transaction accounting year with the current accounting year.

Free Microsoft Office alternative

Looking for a Reliable and Free Office Suite?

While WPS Office does not feature a database management tool like Access, it serves as a powerful, lightweight, and free alternative to Microsoft Office for your document, spreadsheet, and presentation needs. Experience familiar interfaces and high compatibility without the hefty subscription fees.

Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight installation that runs smoothly on Windows, Mac, Linux, iOS, and Android.Free to use with a familiar, easy-to-navigate user interface.Seamless migration from Microsoft Office for standard daily productivity tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is it dangerous to let users modify query design in Access?

Allowing end-users to modify the underlying query design can lead to accidental deletion of database objects, corrupted criteria, or exposure of sensitive data structures. Using forms bound to a settings table is a much safer, controlled alternative.

What does a validation rule of '=1' do in an Access settings table?

Setting a validation rule of '=1' on the primary key field ensures that users can only ever create a single row in that table. This prevents duplicate configuration records, ensuring the query always references exactly one set of start and end dates.

Can I use this one-row table method without joining it to other tables?

Yes. By adding the one-row settings table to the query design without joining it to your main data tables, Access creates a Cartesian product. Since the settings table only has one row, it safely applies those same date variables to every row in your data query without duplicating results.

Office Quick Solutions