Solved

How to use YES/NO checkboxes in a query?

Posted on 2011-09-11
5
214 Views
Last Modified: 2012-08-13
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
Comment
Question by:cssc1
5 Comments
 

Expert Comment

by:Gene-Math
ID: 36520196
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
 

Author Comment

by:cssc1
ID: 36520544
Same problem, see attached file
YES-NO.accdb
0
 
LVL 10

Assisted Solution

by:plummet
plummet earned 400 total points
ID: 36521647
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
 
LVL 10

Accepted Solution

by:
plummet earned 400 total points
ID: 36521655
Here's your database with the amended query. YES-NO.accdb
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 36521844
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question