Filtering a Form by three different checkbox fields

[Varda]

MS Access 2010



I have a form with three fields that are of the Yes/No type (checkbox).

I'd like to create a command button that would filter the form by which checkbox is Yes.

At the same time I'd also like to allow the user to filter by another field (for Status).

The code I have for applying the filter of status on the form is:

   
    Dim strStatus As String
    Dim strFilter As String

 Select Case Me.fraStatus.Value
      Case 1
          strStatus = "='Active'"
      Case 2
          strStatus = "='On Hold'"
      Case 3
          strStatus = "='Closed'"
  End Select

strFilter = "[Status]" & strStatus

With Forms!MainForm
        .Filter = strFilter
        .FilterOn = True
End With

How do I add the criteria of a checkbox to this code?

[June7]

Looking at your db. I see the checkboxes are bound. Controls for input of filter criteria must be unbound, like you have for Status and ReportType on right side of form.

Should user be able to select only one of the 3 for filter criteria?

[Varda]

The three checkboxes that are bound are data entry fields (they're located at the bottom half of the page). The controls I'm using for filtering are on the right side of the screen, below the the Status and Report Type filter option.

User should select only 1 of the checkboxes for filter criteria. But I'm also interested how I'd do it if i allowed for 2 checkboxes?

[June7]

Still need some unbound controls for input of filter criteria.

How do you want the yes/no fields handled? If you use unbound checkbox for filter criteria, what do you want to happen when checked or unchecked? If checked select records where field is true, if unchecked select records where field is false? An unbound checkbox can be set with triple state enabled.

Filtering on multiple yes/no fields along with other type fields can be confusing. Should you use AND or OR operators? Mixing AND and OR operators is particularly tricky - parens are critical. Picture the resulting statement and follow the logic. Will you get the desired output?