By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,275 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,275 IT Pros & Developers. It's quick & easy.

Dynamic Form to Filter Queries by Month & Year combo boxes

P: 5
Table: tbl_data contains the following fields
qry_rpt_src_MonthlyRenewals contains the same fields
Expand|Select|Wrap|Line Numbers
  1. ID    Firstname    LastName    StartDate    EndDate
  2. 1    First1    Last1    01/08/2018    01/08/2018
  3. 881    First2    Last2    09/05/2019    27/06/2019
  4. 1126    First3    Last3    12/07/2019    30/06/2020
  5. 1208    First4    Last4    07/08/2019    31/07/2020
  6. 1364    First5    Last5    16/09/2019    31/07/2020
  7. 1532    First6    Last6    25/10/2019    30/09/2020
  8. 1976    First7    Last7    05/02/2020    31/01/2020
  9. 1983    First8    Last8    05/02/2020    31/10/2020
  10. 1984    First9    Last9    31/01/2020    31/08/2020
  11.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT zz_frm_SearchMonths.SearchMonth, zz_frm_SearchMonths.SearchMonthName FROM zz_frm_SearchMonths ORDER BY zz_frm_SearchMonths.SearchMonth; 
  2.  
Table: tbl_zz_SearchMonth contains the following fields
Expand|Select|Wrap|Line Numbers
  1. SearchMonth    SearchMonthName
  2. 01    January
  3. 02    February
  4. 03    March
  5. 04    April
  6. 05    May
  7. 06    June
  8. 07    July
  9. 08    August
  10. 09    September
  11. 10    October
  12. 11    November
  13. 12    December
  14.  
cbo_SearchYearFilter
Expand|Select|Wrap|Line Numbers
  1. SELECT [zz_frm_SearchYear].[SearchYear] FROM zz_frm_SearchYear ORDER BY [SearchYear]; 
Table: tbl_zz_SearchYear
Expand|Select|Wrap|Line Numbers
  1. SearchYear
  2. 2018
  3. 2019
  4. 2020
  5. 2021
  6. 2022
  7. 2023
  8. 2024
  9. 2025
  10.  
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.
2 Weeks Ago #1
Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,364
Just include a WHERE clause that includes your search criteria based upon both Combo Boxes.
2 Weeks Ago #2

P: 5
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.
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,364
The WHERE clause goes right before your ORDER BY clause.
Expand|Select|Wrap|Line Numbers
  1. "WHERE Year([EndDate]) = '" & Me.cbo_SearchYearFilter & _
  2.     "' 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.
2 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,660
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2. FROM ...
  3. WHERE (Format([EndDate],'yyyymm')=Forms!frm_Admin_Reports!cbo_SearchYearFilter & Forms!frm_Admin_Reports!cbo_SearchMonthFilter)
2 Weeks Ago #5

P: 5
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?
1 Week Ago #6

NeoPa
Expert Mod 15k+
P: 31,660
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.
1 Week Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.