Applying multiple, stacking filter criteria via different buttons/toggles

Question

I'm developing a relatively simply Access database at work, learning as I go. I'm currently attempting to implement some buttons that will allow users to more easily apply/remove filters. For example, I have three columns I'm interested in filtering: FlaggedImpact, FlaggedDeployability, and IsRedacted.

I was able to make individual buttons that will toggle filters on those columns. Here's what I wrote for a toggle that successfully turns on/off a filter for IsRedacted:

Private Sub ToggleRedacted_Click()
    If ToggleRedacted.Value = True Then
        newFilter = "[IsRedacted] is True"
    Else
        newFilter = "[IsRedacted] is False"
    End If
    Me.Filter = newFilter
    Me.FilterOn = True
    Me.Refresh
End Sub

If I click this once, I'll only see entries that have been redacted. If I click it again, I'll only see entries that have not been redacted. However, if I make a similar toggle for FlaggedImpact or FlaggedDeployability, clicking any of them will overwrite any previous filters. 

What I would like to be able to do is have three toggles (buttons? whatever?) that independently add/remove filter properties, so that I can, e.g,. click the IsRedacted and FlaggedImpact toggles to filter the form and display only entries that have been redacted and flagged for impact.

I know I can add to the existing filter by writing

newFilter = currentFilter & " AND [FlaggedImpact] is True"

However, I'm not sure how to remove a filter property, or to check what the current filter state is.

I know I can use 

currentFilter = Me.Filter

But I don't know if it's possible to pull out particular parts of the filter, or parse it the way I want to. It also seems like it would quickly get ridiculous to have nested if/then statements to parse the current filter.

Any suggestions--including a recommendation to not use toggles this way--would be much appreciated. Thanks!

 

Answer 1

Hi Becca. The technique I use is to have a specific filter for each field I want the user to search. For example, my filter might look something like this:

Me.Filter = "FlaggedImpact=True AND FlaggedDeployability=True AND IsRedacted=True"

Of course, if the wants to turn off FlaggedDeployability, the filter will then look like this:

Me.Filter = "FlaggedImpact=True AND FlaggedDeployability=False AND IsRedacted=True"

So, now the problem is how do we implement it using Toggle buttons? You might try something like this:

Me.Filter = "FlaggedImpact=" & Me.ToggleForFlagImpact _
    & " AND FlaggedDeployability=" & Me.ToggleForFlaggedDeployability _
    & " AND IsRedacted=" & Me.ToggleForIsRedacted

Hope it helps...

Answer 2

Thanks for the quick reply. My concern is that this seems to require an individual button for each possible permutation. So rather than having three buttons (one to toggle deployability, one to toggle impact, and one to toggle redacted) I would need six (three factorial) buttons, one for every possible combination of filters.

I'm wondering if buttons (or toggles) is actually the right way to do this. =/

It seems like I could make public variables to track if a certain filter has been turned on or off, and use that as a way to allow each button to rewrite the filter as it goes. Pro would seem to be it lets me use buttons without a ton of extra work, con is that it requires a lot of If/Then statements and some careful construction (and probably breaks a zillion best practices).

Answer 3

Any suggestions--including a recommendation to not use toggles this way--would be much appreciated. Thanks!

Hi Becca,

The problem with toggle buttons is that it is or False or True, but I assume there are also situations where you are not interested in its value.

My way of working is to have a form with the three unbound controls FlaggedImpact, FlaggedDeployability and IsRedacted. For each of the controls you can select the value "True", "False" or "N.A.", of which only one value is allowed.

After all the selections are made, you can press an "Apply"-button. By pressing you construct the Filter string using only the controls that have a "True" of "False".

Imb.

Answer 4

Hi Becca. A toggle button can have one of two values, so you don't have to have 6 of them because 3 can cover all scenarios. I didn't tell you the other part of my approach because I thought what I posted was enough if your fields were all Yes/No fields. If it's the case, then your toggle buttons will either have a True or False value, and checking all buttons at the same time should give you all the scenarios the user can throw at it. What part are you not clear yet?

Answer 5

It seems like I could make public variables to track if a certain filter has been turned on or off, and use that as a way to allow each button to rewrite the filter as it goes. Pro would seem to be it lets me use buttons without a ton of extra work, con is that it requires a lot of If/Then statements and some careful construction (and probably breaks a zillion best practices).

Hi Becca. Truth is, I also use public variables sometimes. So, if it is something you're interested in pursuing, we can also try going this way. However, if you're just filtering Yes/No fields, what I proposed should be enough for now - until you need to add other field data types into the mix. Just my 2 cents...

Answer 6

Oh I see. You're using the state of each of the toggles to add to the filter. Clever!

Answer 7

Oh I see. You're using the state of each of the toggles to add to the filter. Clever!

Correct! As long as you're just concerned about Yes/No fields, Toggle buttons should be fine. Cheers!

Answer 8

It seems to me that the elephant in the room here is the point made by Imb.  At present you are assuming that the user will want to return rows based on all three criteria, rather than only one or two or all three of them.  There are two ways of doing the latter:

1.  Build the string expression by examining all three controls, each of which will have three possible values as Imb describes, True, False and N/A.  Combo boxes can be used for this, with the default value for each being N/A and Nulls being prohibited.  The string expression will then include only those where the N/A option has not been chosen.

2.  Build a string expression which uses simple Boolean logic e.g.

Me.Filter = _
     "(FlaggedImpact = " & Me.cboFlaggedImpact  & " OR " & _
     (Me.cboFlaggedImpact = "N/A") & ") AND " _
     "(FlaggedDeployability = " & Me.cboFlaggedDeployability & " OR " & _
     (Me.cboFlaggedDeployability = "N/A") & ") AND " & _
     "(IsRedacted = " & Me.cboIsRedacted & " OR " & _
     (Me.cboIsRedacted  = "N/A") & ")"

Me.FilterOn = True

In either case the filter can be applied by putting the code in the Click event procedure of a separate 'Confirm' button, or in a function called as the AfterUpdate event property of each of the three combo boxes.  With the latter approach the filter will be applied progressively as a selection is made in each control.

If, for instance, True were selected for FlaggedImpact, False for FlaggedDeployability, and N/A for IsRedacted, rows would be returned where FlaggedImpact = True And FlaggedDeployability = False, but regardless of the value of IsRedacted.  

---

Ken Sheridan, Stafford, England

Answer 9

On reflection the second approach above wouldn't work as N/A would be treated as a parameter in the first part of each OR operation.  It would have to be done like this:

Me.Filter = _
      IIF(Me.cboFlaggedImpact = "N/A","TRUE","FlaggedImpact = " & Me.cboFlaggedImpact) & " AND " _
      IIF(Me.cboFlaggedDeployability = "N/A","TRUE","FlaggedDeployability = " & Me.cboFlaggedDeployability) & " AND " _
      IIF(Me.cboIsRedacted = "N/A","TRUE","IsRedacted  = " & Me.cboIsRedacted)

---

Ken Sheridan, Stafford, England