Others

Filter a Microsoft Access Query for the Current Month

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 868 views

Question details

Filter the Closed date field in an Access query to show records from the current month.

Filter a Microsoft Access Query for 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query in SQL View

In Access, right-click the query in the Navigation Pane and select SQL View.

2
Add the date condition

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.

3
Remove the row-level HAVING condition

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.

4
Run the query

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 current calendar month
WHERE versus HAVING: WHERE filters individual records before grouping; HAVING filters grouped or aggregate results.
Free Microsoft Office alternative

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.

Filter exported date columns in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for working with existing spreadsheetsMove compatible Office documents into a lightweight office suite
microsoft office alternative - wps office

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.