Code:
Public Function fcnBuildQuery() As String
Dim sCrit As String
Dim nCtr As Long
Dim ChkA As Boolean, ChkB As Boolean, ChkC As Boolean, ChkD As Boolean, ChkE As Boolean, ChkF As Boolean
Dim collectChks As Collection
ChkC = True
ChkF = True
sCrit = "WHERE "
Set collectChks = New Collection
If ChkA = True Then collectChks.Add "ChkA"
If ChkB = True Then collectChks.Add "ChkB"
If ChkC = True Then collectChks.Add "ChkC"
If ChkD = True Then collectChks.Add "ChkD"
If ChkE = True Then collectChks.Add "ChkE"
If ChkF = True Then collectChks.Add "ChkF"
If collectChks.Count = 0 Then
' no checkboxes checked
Exit Function
End If
nCtr = collectChks.Count
Select Case nCtr
Case 1
sCrit = sCrit & collectChks.Item(1) & " = True "
Case Else
While nCtr > 0
sCrit = sCrit & collectChks.Item(nCtr) & " = True OR "
nCtr = nCtr - 1
Wend
End Select
if collectChks.count > 1 then sCrit = Left(sCrit, Len(sCrit) - 4) ' strip off the OR
fcnBuildQuery = sCrit
Debug.Print sCrit
End Function
Here's a fairly simple way to build the criteria. It uses a VBA collection.
The collection will contain the names of the checkboxes that have a value of TRUE
For simplicity I've substituted variables for the checkboxes and given a couple of the variables TRUE value.
This doesn't have to be a function, I just used that to contain the code and get it to work.
If you want specific combinations of checkboxes, you could expand the Select Case to handle that.