Dynamic Form to Filter Queries by Month & Year combo boxes

[SueHopson]

Table: tbl_data contains the following fields
qry_rpt_src_MonthlyRenewals contains the same fields

ID    Firstname    LastName    StartDate    EndDate
1    First1    Last1    01/08/2018    01/08/2018
881    First2    Last2    09/05/2019    27/06/2019
1126    First3    Last3    12/07/2019    30/06/2020
1208    First4    Last4    07/08/2019    31/07/2020
1364    First5    Last5    16/09/2019    31/07/2020
1532    First6    Last6    25/10/2019    30/09/2020
1976    First7    Last7    05/02/2020    31/01/2020
1983    First8    Last8    05/02/2020    31/10/2020
1984    First9    Last9    31/01/2020    31/08/2020
 

I would like to create a form with prepopulated combo boxes that the user can select to filter the end date of the query by Month and Year (not always current month and year)

Currently I have frm_Admin_Reports which displays the following Unbound combo boxes
cbo_SearchMonthFilter

SELECT zz_frm_SearchMonths.SearchMonth, zz_frm_SearchMonths.SearchMonthName FROM zz_frm_SearchMonths ORDER BY zz_frm_SearchMonths.SearchMonth; 
 

Table: tbl_zz_SearchMonth contains the following fields

SearchMonth    SearchMonthName
01    January
02    February
03    March
04    April
05    May
06    June
07    July
08    August
09    September
10    October
11    November
12    December
 

cbo_SearchYearFilter

SELECT [zz_frm_SearchYear].[SearchYear] FROM zz_frm_SearchYear ORDER BY [SearchYear]; 

Table: tbl_zz_SearchYear

SearchYear
2018
2019
2020
2021
2022
2023
2024
2025
 

I know there is a way to merge the search fields to pull all records with that expiry date from the query using a button-run macro, but it's been so long since I've done this I simply can't remember.

Suggestions for simplifying also appreciated. Please do not suggest simply editing in the query as the end users are incapable.

[twinnyfo]

Just include a WHERE clause that includes your search criteria based upon both Combo Boxes.

[SueHopson]

If I could remember "WHERE" that goes, that would most likely solve my problem (ie, VBA query, access renewal query, etc) :) Since I can't, a little more guidance would be helpful.

[twinnyfo]

The WHERE clause goes right before your ORDER BY clause.

"WHERE Year([EndDate]) = '" & Me.cbo_SearchYearFilter & _
    "' AND Month([EndDate]) = '" & Me.cboSearchMonthFilter & "' "

You will have to convert my smart quotes to straight quotes. My iPad doesn’t know how to be dumb....

Hope this hepps.

[NeoPa]

I'd do it a little differently. If, as you say, you don't want the query (ies?) to be updated then include references in your queries to the Form Controls directly.

I don't often advise this as it creates dependencies that can cause you headaches later on, but if they're your restrictions then this is a possible route.

SELECT ...
FROM ...
WHERE (Format([EndDate],'yyyymm')=Forms!frm_Admin_Reports!cbo_SearchYearFilter & Forms!frm_Admin_Reports!cbo_SearchMonthFilter)

[SueHopson]

My thanks to you both, I've had a chance to review and while both are excellent suggestions for this group/build I think NeoPa's suggestion will be more effective.

While I can now get the report to open, regrettably it is not displaying any records and I am certain it has to do with the End-Date format. When manually manipulating the query directly, the [End_Date] format is dd/mm/yyyy and the Criteria used was Like "*/01/2020" (as an example).

I've tried manipulating the Format [End_Date] field, but nothing seems to be effective. Suggestions?

[NeoPa]

Hi Sue.

While this question may appear to be connected, as in that it's connected to the same work you're doing, technically they are different issues, thus a new thread is required to ask this.

Feel free to link to this one in your new thread if you believe it may add helpful context.