Filter a Microsoft Access Query for the Current Month
Question details
Filter the Closed date field in an Access query to show records from the current month.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A grouped query uses a separate query as a criterion for the Closed date field.
- Observed behavior
- Referencing the separate query in a HAVING clause can cause an aggregate-function error or a syntax error.
Confirm that Table_SPAP.Closed is a Date/Time field and decide whether you need the calendar's current month or the month of the latest StartDate in Table_InspLots.
Filter Closed dates to the current calendar month
Use a row-level WHERE condition before grouping to include dates in the current month and year.
In Access, right-click the query in the Navigation Pane and select SQL View.
Before GROUP BY, add: WHERE YEAR(Table_SPAP.Closed) = YEAR(Date()) AND MONTH(Table_SPAP.Closed) = MONTH(Date()). If the query already has a WHERE clause, add the two conditions to it with AND instead of creating another WHERE clause.
Remove the HAVING condition that compares Table_SPAP.Closed with [Vendor_ThisMonth]![Month/Year]. Keep any HAVING conditions that genuinely filter aggregate results, and check that all parentheses are balanced.
Select Run on the Query Design ribbon and verify that the results contain Closed dates from the current month and year.

Filter Closed dates to the latest month in another table
Use a scalar subquery when the relevant month is determined by the latest StartDate rather than today's date.
Review exported Access data in WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with exported data in a familiar spreadsheet interface. Its Microsoft Office format compatibility helps you move existing files into WPS, though it does not run or repair Access queries.

Frequently Asked Questions
Why should the date condition be in WHERE instead of HAVING?
In Access SQL View, put the Closed date condition in WHERE before GROUP BY to filter individual records. HAVING is intended to filter grouped or aggregate results.
Does the current-month filter also check the year?
Yes. YEAR(Table_SPAP.Closed) = YEAR(Date()) checks the year, and MONTH(Table_SPAP.Closed) = MONTH(Date()) checks the month.
Do I need a separate query to find the current month?
No. In SQL View, use Date() in the WHERE clause to compare the Closed field with the current month and year.
What if the latest StartDate is not in the current month?
The MAX(StartDate) subquery filters to the month of the latest recorded date, which may differ from the current calendar month. Use the Date() condition if you need the current month instead.




