Solved Filter Form by Value List Combo Box (1 Viewer)

tmyers

Active member
Local time
Yesterday, 23:55
Joined
Sep 8, 2020
Messages
757
I have been reading several different post, but have not yet found one that I understand to accomplish what I am after.
My form is a split-form and contains a date column (the table behind the form is the data type Date as well). I am trying to use a value list combo box to filter the form by months, so the combo box will display January, February, March etc. and filter the form accordingly.

So with the column being formatted as 01/01/21, how could I use the combo box selection of January to filter to that?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:55
Joined
Oct 29, 2018
Messages
18,742
You could use a two column combo where the first (hidden) column is the month number.
 

tmyers

Active member
Local time
Yesterday, 23:55
Joined
Sep 8, 2020
Messages
757
That would work. I don't how to set values for a multi-column combo box though. Going to have to read up on that.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:55
Joined
May 21, 2018
Messages
6,259
Typing a multicolumn value list is kind of a PITA IMO. I would simply make the table. I could do that faster easier. If you want you can do the value list.
tblMonths
monthNumber
monthName

1 January
2 February
....

Then bind this to the combo but hide the first column
boundColumn: 1
columnWidths: 0'; 1"

I would base the form off a query and add an additional field
Select DateField, Month([DateField]) as MonthNumber,....

Now filter the calculated field MonthNumber by the combobox MonthNumber.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:55
Joined
May 21, 2018
Messages
6,259
You can do it the other way to.
In your query for the form
Select DateField, format([dateField],"MMMM") as StrMonth
Now in your query you will have a column with January, February,....
Now you can have a single column combobox with the month name since you have the month name as a column in the form.
 

tmyers

Active member
Local time
Yesterday, 23:55
Joined
Sep 8, 2020
Messages
757
I like your approach better then what I started doing.
The form is already based on a query of the table and I made the table for the months like you suggested. I am however not following how to add it to the forms query.

Edit:
I suppose I should add this as it will come up eventually. This method appears to not be year specific (which I like as I want another combo to select the year from). Am I correct in assuming that it would not filter by the year, but purely the month?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:55
Joined
May 21, 2018
Messages
6,259
In your forms query I assume you pick your date field from some table and add it to the query. In design view of the query you can add calculated control or type it in sql.
To add a calculated field type a new name, colon, then the expression
MonthNumber: Month([YourDateField])

That will create a calculated field in the query called MonthNumber.
Or
YearNumber:Year([YourDateField])

So you will have 2 calculated fields in the query.
1/1/2021 1 2021
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:55
Joined
May 21, 2018
Messages
6,259
To filter by year and month you might need two combos. Or you could do something like this with one combo.
YearMonth: format([DateField],"YYYY-MMM")
this makes a calculated control in the forms query that has values like
2021-Jan
2021-Feb
etc.

Then in your combo box you need to return all year month combos in the same format. I would return only those years and month that exist in the database.
Now your combo would include similiar values from the first year,month to last
2019-Jan
2019-Feb
...
2021-Nov
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:55
Joined
May 21, 2018
Messages
6,259
Here is an example of the latter. The tricky part is creating the DISTINCT query to pull the viable year months from the actual data. Having two combos would require more code, but then you can pick one or both. Filter by Year, Filter by Month, Filter by Year and Month.
 

Attachments

  • datefilter.accdb
    1,012 KB · Views: 214

tmyers

Active member
Local time
Yesterday, 23:55
Joined
Sep 8, 2020
Messages
757
I was planning on making the month and year two separate combos, as that is what the boss would like.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 23:55
Joined
May 21, 2018
Messages
6,259
See demo. Filter three ways Month, Year, both
 

Attachments

  • datefilter2.accdb
    1,012 KB · Views: 285

tmyers

Active member
Local time
Yesterday, 23:55
Joined
Sep 8, 2020
Messages
757
I was able to get it fully working with no hiccups (yet!). Thanks for your help as always Majp!
 

Users who are viewing this thread

Top Bottom