Solved

How to use YES/NO checkboxes in a query?

Posted on 2011-09-11
5
212 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

943 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now