How to Use Update Queries with Date Criteria in Microsoft Access
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.

- 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.
Always create a backup copy of your Microsoft Access database before running bulk update queries to prevent accidental data loss.
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.
In Microsoft Access, go to the Create tab and select Query Design. Close the Show Table dialog and switch to SQL View.
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#;
Click the Run button (red exclamation mark) in the Design tab to update the matched records.
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 a Single Update Query with the IIf Function
Alternatively, you can use the IIf (Immediate If) function within a single query to evaluate the date criteria and assign values conditionally.
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.

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.




