How to Query the Last Day of the Previous Month in MS Access
Question details
The user needs to formulate an exact query criterion in Microsoft Access to filter and return records that match the last day of the previous month.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Filtering historical or monthly asset database records using specific date criteria in MS Access Design View.
- Observed behavior
- The user requires the correct syntax for the query, and encountered an issue where applying the date criterion initially returned no results.
Verify that the target field in your Access table is explicitly formatted with a Date/Time data type, and ensure the stored records do not contain hidden time components that could prevent exact date matching.
Use the Date subtraction method
The quickest and most common method to calculate the last day of the previous month by subtracting the current day number from today's date.
By utilizing the built-in Date() and Day() functions, Microsoft Access can automatically roll back to the final day of the preceding month regardless of leap years or month lengths.
Open your Access database, navigate to the Navigation Pane, right-click your target query, and select 'Design View'.
Find the column containing your specific date field in the query design grid at the bottom of the screen.
Click into the 'Criteria' row directly under your date field and type the exact expression: Date()-Day(Date())
Navigate to the 'Query Design' tab on the top ribbon and click the red exclamation mark icon labeled 'Run' to view the filtered records.
Use the DateAdd and DateSerial functions
An alternative, highly explicit expression using built-in date functions to construct the exact last day of the prior month.
Troubleshoot empty query results
Fix issues where the query returns zero records despite the underlying table containing data for the target date.
Easily Manage and Filter Date Records with WPS Office
While Microsoft Access requires complex SQL expressions for date queries, WPS Spreadsheet provides a free, lightweight alternative with intuitive built-in date filters. You can seamlessly manage, filter, and analyze large datasets without typing any code.
- 1. Import Your Data: Export your Access table to an Excel format and open it directly in WPS Spreadsheet.
- 2. Enable Data Filtering: Select your header row, navigate to the 'Data' tab on the top ribbon, and click 'AutoFilter'.
- 3. Filter by Previous Month: Click the drop-down arrow on your date column, select 'Date Filters', and choose 'Last Month' to instantly display the relevant records.
Frequently Asked Questions
How can I test my Access date expressions before running a query?
You can test any date expression using the Immediate Window. Press Ctrl+G while in Access to open the VBA editor, type a question mark followed by your expression (e.g., ?Date()-Day(Date())), and press Enter. The calculated date will print on the next line.
Why does my date query return no results even if the date visually matches?
The most common reason is that your date field contains a time component (e.g., 12:00:01 AM). Standard date criteria look for exactly midnight. You can resolve this by using the DateValue() function in your query to isolate just the date portion before applying the criteria.
Can I query a date range for the entire previous month instead of just the last day?
Yes. To return all records from the previous month, enter this into your Criteria row: Between DateSerial(Year(Date()), Month(Date())-1, 1) And DateSerial(Year(Date()), Month(Date()), 0). This filters from the 1st day to the last day of the prior month.
