?
Solved

How to use YES/NO checkboxes in a query?

Posted on 2011-09-11
5
Medium Priority
?
234 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 1600 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 1600 total points
ID: 36521655
Here's your database with the amended query. YES-NO.accdb
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

830 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