Multiple Criteria Filter button in Access Form

[bmhuettinger]

Good morning,
I have a form that uses conditional formatting to color code an [OpenOrder] date field. There are currently 5 different conditions that result in four different colors for the field. The intended users of the form are not super familiar with Access so I'd like to create a button that can essentially filter based on this criteria - simplistically: red, yellow, green or grey. I know that unlike Excel, Access doesn't offer a filter based on color, but I'm having difficulty even trying to find/create "code" that would allow me to program a button based on all five of the aforementioned criterion. Also, I don't think a combo box or list box would work, as the results need to be in the main form so that the fields can be edited and updated with new data.
Any direction would be greatly appreciated! Thanks!

 

[isladogs]

I'm sure you're right that you can't filter by colour (or not easily anyway)

First, you need to setup your conditional formatting for each of the conditions.
Something similar to the screenshot I just posted here

https://www.access-programmers.co.uk/forums/showpost.php?p=1540891&postcount=4

Then you need to filter using the same expressions as you have used for conditional formatting

 

[bmhuettinger]

Thanks for the quick reply! My conditions have already been set and seem to be working great. What I can't figure out, is how I can copy these conditions as expressions (presumably "nested iifs") into the controls of a button so that the one "click" will apply the filters of 5 conditions all at once. Does that make sense? I may not be explaining my issue properly.

 

[isladogs]

I doubt you want all 5 filters applied at once or you'll probably end up with no records.

Suggest you have a combo box with the 5 colour names.
Then apply your filter using the after update event of the combo

I would use Select Case rather than multiple Ifs
Something like

 private sub cboFilter_AftrUpdate()

Select Case me.cboFilter

Case "Red"
'add red filter expression here

Case "Green"

...

End Select

'add any other code you need here such as
 me.requery

End Sub

 

[bmhuettinger]

Thank you! This an example of the expression that changes the formatting to RED

([PROMDATE]<Date()) And ([NOTE1] Not Like "*STOCK*")

As you can see, it's not a complete expression because the Conditional Formatting drop-down menu takes care of some details "behind the scenes", I think. I don't know how to turn this into proper code.

 

[isladogs]

If you can post a stripped down copy of your db with the relevant form and other relevant items then i'll have a look at it.

Remove or modify anything confidential first

As you have less than 10 posts, you'll need to zip it

 

[bmhuettinger]

thank you so much for your time and patience. I actually figured out a way to do it by creating a query and then a Filter macro based on that query. I then made a button based on the macro. Have a great weekend!