Instead of creating a queries for every month, depending how you call the query:
a) Create a parametrized query. I prefer SQL view, so you create something like
Code:
SELECT YourDate, ... FROM YourTable WHERE Format(YourDate, "yyyymm") = [Enter month in format YYYYMM]
and save it. Now when you ran this saved query, you are asked for month. You enter it like 202006, and you get the result for previous month.
b) Set report parameters in some form, and call the report from click event of button on same form. for this
1. You create a report with Source like "SELECT YourDate, CInt(Format(YourDate, "yyyymm")) AS parMonth, ... FROM YourTable";
2. You add a control (text box or combo box), where you enter integer report parameter in format "YYYYMM". E.g. txtYYYYMM ;
3. You add a button, with OnClick event calling the report. The code for this will be like
Code:
DoCmd.OpenReport "YourReport", acViewNormal, "parMonth = " & Me.txtYYYYMM
Now you enter into control a value like 202006, and click the button - and the report for previous month is returned.
3. Like 2, but clicking button calls a VBA procedure, which reads parameter value from control, then creates a QueryString variable(s) with parameter value taken into account (there is no need to calculate parMonth field, as a WHERE clause for QueryString is constructed instead), and then updates source of some form to display result data, or runs some action(s) with results of query, e.g. updates tables, exports query results to some 3rd-party database, or saves query results e.g. as text/csv/html files, etc.