Others

How to Use Update Queries with Date Criteria in Microsoft Access

Ayan MasoodAyan Masood Nov 4, 2026 870 views

Question details

The user needs to update specific records to one value and all other records to a different value in Microsoft Access using precise date range criteria.

How to Use Update Queries with Date Criteria in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Updating records conditionally based on whether a date field falls within a specific month or date range.
Observed behavior
Records matching the date criteria are successfully updated to a target value, while records outside the criteria are updated to a secondary value, with a focus on correctly handling hidden time components.
Before you start

Always create a backup copy of your Microsoft Access database before running bulk update queries to prevent accidental data loss.

Solution 1Recommended

Use Two Sequential Update Queries with Exclusive End Dates

Execute two separate update queries to first target the specific date range, and then target the remaining records. Using an exclusive end date ensures accurate handling of date/time fields.

When dealing with Date/Time fields in Access, using `>=` for the start date and `<` for the day after your end date prevents issues where time fractions cause records on the final day to be skipped.

1
Open the Query Design View

In Microsoft Access, go to the Create tab and select Query Design. Close the Show Table dialog and switch to SQL View.

2
Enter the First Update Query

Type the first SQL statement to update records within the target date range. For example: UPDATE TblWcSettle INNER JOIN WCtab ON TblWcSettle.ClaimNo = WCtab.PrpClaimNo SET TblWcSettle.Branch = 'AD' WHERE TblWcSettle.AcceptDate >= #12/1/2020# AND TblWcSettle.AcceptDate < #12/31/2020#;

3
Execute the First Query

Click the Run button (red exclamation mark) in the Design tab to update the matched records.

4
Run the Second Update Query

Clear the SQL view and enter the second query to update the remaining records: UPDATE TblWcSettle INNER JOIN WCtab ON TblWcSettle.ClaimNo = WCtab.PrpClaimNo SET TblWcSettle.Branch = 'DB' WHERE TblWcSettle.AcceptDate < #12/1/2020# OR TblWcSettle.AcceptDate >= #12/31/2020#; Run this query to complete the process.

Use Two Sequential Update Queries with Exclusive End Dates
Handling Time Values: Using '< #12/31/2020#' effectively includes all times up to 11:59:59 PM on 12/30/2020, avoiding the pitfalls of the 'Between' operator.
Free Microsoft Office alternative

Looking for a Lightweight Office Suite Alternative?

While Microsoft Access is a powerful database tool, handling everyday documents, spreadsheets, and presentations shouldn't require an expensive subscription. WPS Office provides a complete, free, and highly compatible alternative for your core productivity needs.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight application that runs smoothly on older or lower-spec devices.Built-in PDF toolkit for easy editing, merging, and conversion.Familiar user interface for a seamless migration from Microsoft Office.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access update query miss records on the last day of my date range?

If your date field includes time data, a criterion like `<= #12/30/2020#` only covers up to midnight (12:00:00 AM) on that day. To include the entire day, use `< #12/31/2020#` instead.

How do I format dates in Microsoft Access SQL queries?

In Microsoft Access SQL, date values must be enclosed in hashtag symbols (#). For example, December 1, 2020, should be formatted exactly as `#12/1/2020#`.

Can I use the 'Between' operator for date and time fields?

Yes, you can use 'Between Date1 And Date2', but use caution if your records contain time values. The 'Between' operator might exclude records on the final date if they occur after 12:00 AM. Using greater than and less than operators is generally safer for precise boundaries.

How do I undo an update query in Access?

Once an update query is executed in Access, the action cannot be undone using a simple 'Undo' command. You must either restore your database from a backup or run a reverse update query to restore the original values if the previous state is known.