• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

How to use YES/NO checkboxes in a query?

How to use YES/NO checkboxes in a query?

DESCRIPTION
I have created three Objects:
1.      Table1
2.      Form1
3.      Query1
Table (Table1) has several checkbox fields in it (see below).
Cars
Boats
Planes
Ships
Motorcycle
Bicycle
Form (Form1) displays the checkboxes so users can easily select what they want.
Query1 (Query1) attempts to filter only records that have one or more of the items checked.

PROBLEM
I do not know what to put in the Criteria field (s) of the query in order to filter records that have one or more items checked?
I do not want to use code to accomplish as I don’t understand the code. It is much easier for me to work with the Criteria.



YES-NO.accdb
0
cssc1
Asked:
cssc1
3 Solutions
 
Gene-MathCommented:
I've updated your access database as an example.

Access uses 0 for false, and -1 for true in the criteria box.

Don't forget to change the forms Rowsource to the query.
0
 
cssc1Author Commented:
Same problem, see attached file
YES-NO.accdb
0
 
plummetCommented:
I may have got the wrong end of the stick, but if all you want to do is for your query to return any records with any of the yes/no boxes ticked then this will do that:

( copy this SQL into the SQL view of an access query, then you can go back to Design view )
SELECT Table1.ID, Table1.Name_1, Table1.Cars, Table1.Boats, Table1.Planes, Table1.Ships, Table1.Motorcycle, Table1.Bicycle
FROM Table1
WHERE (((Table1.Cars)=True)) OR (((Table1.Boats)=True)) OR (((Table1.Planes)=True)) OR (((Table1.Ships)=True)) OR (((Table1.Motorcycle)=True)) OR (((Table1.Bicycle)=True));

Open in new window

0
 
plummetCommented:
Here's your database with the amended query. YES-NO.accdb
0
 
mbizupCommented:
The above will work.

Another simple alternative is to check the 'sum' of all the yes/no fields with the following WHERE clause ( in the SQL view of your query):

Where Cars + Boats + Planes + ships + Motorcycle + Bicycle <> 0

Open in new window

Explanation:

True = -1
False = 0

So if any of the boolean fields is true (checked), the sum of them will not be zero.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now