Query Based on Three Yes/No Checkboxes

[Brian]

I think I have a simple question. I have a database where up to three
checkboxes can be selected. I would like to create a query, or report, that
will show if any two of the three are selected. I'm a novice at this, so
please feel free to elaborate as much as you can! I keep reader about all
this code, and I don't even know where to place that. I'm used to looking in
the query design view (field, show, criteria, or)...and I can't figure out
how to put that together to make this query work. Thanks in advance!!

 

[Rob Parker]

Hi Brian,

Add a calculated field to your query, thus:

2True: IIf((([A] And ) Or ([A] And [C]) Or ( And [C])) And Not ([A]
And And [C]),"2 Checked","")

If you don't need to exclude all three being checked (ie. you want to know
if at least two are checked), you can change this to:

2True: IIf(([A] And ) Or ([A] And [C]) Or ( And [C]),"At least 2
checked","")

HTH,

Rob

 

[Allen Browne]

Access uses -1 for True, and 0 for False. Therefore if you add the 3 fields
and get -2 or -3, then 2 or 3 of them are checked.

For this example, we will assume the 3 yes/no fields are named Red, Green,
and Blue. Create a query, and type this into the Field row:
[Red] + [Green] + [Blue]
Then in the Criteria row under that, enter:
<= -2
The query will return only the rows where at least 2 boxes are checked.

If you want to do it from a command button on a form, the Click event
procedure for the button would be:

Private Sub cmdPreview_Click()
Dim strWhere As String
strWhere = "[Red] + [Green] + [Blue] <= -2"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

 

[Brian]

Thank you (both) so much for your time and help! That did the trick and I'm
"up and running!"

Fantastic!!

Brian

Access uses -1 for True, and 0 for False. Therefore if you add the 3 fields
and get -2 or -3, then 2 or 3 of them are checked.

For this example, we will assume the 3 yes/no fields are named Red, Green,
and Blue. Create a query, and type this into the Field row:
[Red] + [Green] + [Blue]
Then in the Criteria row under that, enter:
<= -2
The query will return only the rows where at least 2 boxes are checked.

If you want to do it from a command button on a form, the Click event
procedure for the button would be:

Private Sub cmdPreview_Click()
Dim strWhere As String
strWhere = "[Red] + [Green] + [Blue] <= -2"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I think I have a simple question. I have a database where up to three
checkboxes can be selected. I would like to create a query, or report,
that
will show if any two of the three are selected. I'm a novice at this, so
please feel free to elaborate as much as you can! I keep reader about all
this code, and I don't even know where to place that. I'm used to looking
in
the query design view (field, show, criteria, or)...and I can't figure out
how to put that together to make this query work. Thanks in advance!!