Apply filter on existing filter from command buttons

[chungy50]

Hi there, I currently have a navigation form which houses an subform that has a set command buttons relating to the organisations different business units. Once you press on one of the buttons, the datasheet within this subform filters to that particular SBU.

I want to have a secondary set of buttons related to the months, but i want the original filter of business unit to still be intact. i.e I want to first click on the business unit to filter that part of the business, and then i want to click on the particular month of interest to further filter out the business unit's performance at that particular month.



So far, i have been able to filter both business units and month via browseto macros but i have only been able to do them independently.

I would provide screenshots but unfortunately there is issues with IP... but thank you in advance!!

Any help is great help.

Regards,
Steven

[June7]

What code are you using? Review: http://allenbrowne.com/ser-62.html

[isladogs]

The way I manage multiple filters is to build the query SQL up in sections using strings, checking each part works then adding the next

The overall SQL might be:

strSQL = strSelect & strFrom & strWhere & strGroupBy & strOrderBy

It should hopefully be obvious what each part of the SQL string refers to.

The filter part in strWhere might itself be made up of several parts

strWhere = " WHERE (" & strDateFilter & strIncidentTypeFilter & strYearFilter & strLocationFilter & strTimeFilter & ")"

Individual filters would be like this:

strDateFilter = "([Incident Date] Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & _
            "# And #" & Format(Me.txtTo, "mm/dd/yyyy") & "#) "

strIncidentTypeFilter = " And [Incident Type] = '" & Me.cboIncidentType.Column(1) & "'"

strLocationFilter = " And Location = '" & Me.cboLocation.Column(1) & "'"

etc...

So in the example below these combine to give

strWhere = " WHERE (([Incident Date] Between #01/21/2016# And #01/08/2018#)  And [Incident Type] = 'TAWG' And Location = 'Cockfosters')"

In this case the output is a chart but the idea applies equally to a list of filtered records



Hope that helps rather than confuses you ...

[ssanfu]

One option is to limit the records returned from a query using the WHERE clause.
The other option is to create a query that returns records limited by the WHERE clause, then use the form filter property to restrict which records are displayed.
See http://www.allenbrowne.com/ser-28.html

With the filter method, you can add more options to the filter or remove the filter and display all of the records.
See http://www.allenbrowne.com/ser-62code.html

I use the filter property method..

[deepakg27]

thanks great post