Solved

How to use YES/NO checkboxes in a query?

Posted on 2011-09-11
5
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

726 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