Filter a Form with Checkboxes

Here is the problem I am currently facing. I have a continous form and all the fields I want to filter on are yes/no fields. So my form shows a list of these records and at the top in the form header I have a check box for each column I would like to be able to filter on (there are 26). I want to be able to check one or more boxes, and then click a search button to execute the filter. I am having trouble with code for this. I have also created another table and instead of yes/no fields I created a text field with restricted values of null or yes. Hoping this would help, but I can not get it to work either. If someone can let me know what field structure is best and help me with the code it would be much appreciated. Thanks!

Have a look at the discussion and sample posted here, whilst the thread deals with filtering by option groups you should find it easy enough to adapt the principals to your own needs.

Here is the code I have so far. The problem is that if I have both check boxes checked it does not filter on both only on one of them. I have tried change the "AND " to "OR ", but no results. Any help would be appreciated.

Dim strWhere As String
Dim lngLen As Long


If Me.ckAQE = True Then
strWhere = "TypeName='AQE' AND "
End If

If Me.CkAirPermit = True Then
strWhere = "TypeName='AirPermit' AND "
End If


lngLen = Len(strWhere) - 4
If strWhere <> "" Then strWhere = Left(strWhere, Len(strWhere) - 4)
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

You will need to use the Or operator, you will also need to ensure the string is closed having removed the trailing Or operator.

Have a look at the attached sample. The form that opens when you start the DB (FRM_PCodes) uses the filter as discussed, the drawback is that if all check boxes are unchecked it will cause an error (this could be trapped and avoided). The other form (FRM_SrchPCode) uses a query to filter the results of a List Box.