How is a select query setup to only select the YES fields?

I have a table with a series of check boxes. I want the quesry to onlt select all the fields that are checked or yes.

I tried putting 'Yes" in the criteria field, but the query will not work?
Who is Participating?
PatHartmanConnect With a Mentor Commented:
You have added my example to the end of your existing statement.  Since we had no idea what your actual SQL statement looked like (I can't download databases), I (and the others) posted a "sample".  It was not intended to be used as is.  We still don't know the name of the column you want to check so the following change is still an EXAMPLE that you need to customize.

Select .......
FROM [Root-Cause_1_tbl] Where [put your own column name here] = True;
mankowitzConnect With a Mentor Commented:
Microsoft usually records true as non-zero and false as zero.  Try this

SELECT * FROM tbl WHERE ckField <> 0;
SimonConnect With a Mentor Commented:
no points please... mankowitz's solution works.

or SELECT * FROM tbl WHERE ckField =TRUE

Boolean fields in Access can be formatted to show 'Yes' and 'No' but the underlying values are -1(TRUE)  and 0 (FALSE)
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

PatHartmanConnect With a Mentor Commented:
To be more generic, you can use True rather than a literal.  Also, be wary of using negative criteria because you may have to add explicit logic to include/exclude null values.  Most people find it easier to understand positive logic so strive for that whenever possible.

Select ... From ... Where YourField = True;

True will be 1 for SQL server and -1 for Jet/ACE but you don't need to worry about it.
Nick67Connect With a Mentor Commented:
Not disagreeing with the logic of @PatHartman's comment

True will be 1 for SQL server and -1 for Jet/ACE  But there are occasions when you do have to worry about it.
The question then becomes: 'How do I deal with my worries?'

1. Can this field be null? and if it can what value do I want null to be?
For this we have our friend Nz() --> Nz(SomeField, True) or Nz(SomeField, False)
Even testing for true has occasions where the results don't do what you expect.  Testing for SomeThing<> 0 however, is pretty much bulletproof.  You will get all items that are not false.  Inverting the result with NOT (SomeThing<> 0) gets you only false items.  Having Null in the mix can be pretty mind-bending.  Tables linked to SQL Server that have bit fields with null in them don't EVER show the nulls in Access -- if you open the tables or queries you will only see False or True -- but the nulls are there -- you can catch glimmers of them when doing Count().  You can occasionally see Count() records that hint at the problem.  One True and two False -- and you'll say to yourself 'WTF?' or WTN -- what the null! -- why are there two counts of False in there?  But really, don't permit Null in a Boolean field.  You'll regret permitting it
2. Will the backend engine ever change?  Testing for -1 will come back to bite you if you ever move the data out of SQL.  Formatting in the table for checkboxes can also occasionally cause grief.  Let you tables and queries show the numbers.  Yes/No and checkboxes can cause grief if you move the backend. I tried putting 'Yes" in the criteria field, but the query will not work?  You already have grief!  Put <> 0 in for a criteria, and your query will come closer to working.

But if you've left null hang around then <> 0 and is not null will be what you require to select all those items that are explicitly set to True.

It's trickier than it looks :)
cssc1Author Commented:
I am getting an error with:

See attached
That's because SELECT * FROM tbl WHERE ckField =TRUE
is an example SQL statement (that would form a complete query if you pasted it into the SQL view of the Access query designer.
To specify criteria for the Yes/No column just enter TRUE in the grid.
cssc1Author Commented:
For some reason I am getting an error with the following SQL statement. I added the recommended sql statement to the existing sql statement.
SELECT [Root-Cause_1_tbl].Q1ID, [Root-Cause_1_tbl].Event_ID, [Root-Cause_1_tbl].Event_No, [Root-Cause_1_tbl].[Human- Machine Interface- labels Needs Improvement], [Root-Cause_1_tbl].[Human- Machine Interface- arrangement or placement], [Root-Cause_1_tbl].[Human- Machine Interface- displays needs improvement], [Root-Cause_1_tbl].[Human- Machine Interface- controls needs improvement], [Root-Cause_1_tbl].[Human- Machine Interface- monitering alertness NI], [Root-Cause_1_tbl].[Human- Machine Interface- plant/ unit differences], [Root-Cause_1_tbl].[Human- Machine interface- excessive lifting], [Root-Cause_1_tbl].[Human- Machine interface- tools/ instruments NI], [Root-Cause_1_tbl].[Work Environment- housekeeping NI], [Root-Cause_1_tbl].[Work Environment- hot/ cold], [Root-Cause_1_tbl].[Work Environment- wet/ slick], [Root-Cause_1_tbl].[Work Environment- lights NI], [Root-Cause_1_tbl].[Work Environment- noisy], [Root-Cause_1_tbl].[Work Environment- obstruction], [Root-Cause_1_tbl].[Work Environment- cramped quarters], [Root-Cause_1_tbl].[Work Environment- equipment guard NI], [Root-Cause_1_tbl].[Work Environment- high radiation/ contamination], [Root-Cause_1_tbl].[Complex System- knowledge- based decision required], [Root-Cause_1_tbl].[Complex System- monitoring too many items], [Root-Cause_1_tbl].[Non- Fault Tolerant System- errors not detectable], [Root-Cause_1_tbl].[Non- Fault Tolerant System- errors not recoverable]
FROM [Root-Cause_1_tbl];
Like thischange
@ Nick,
Why would you just not check for True?  I agree that there are some situations when you have a code list that you might need to use negative logic to reduce complexity but in the case of True/False, why go negative?  People naturally understand positive statements but negative ones, especially compound negative ones tend to confuse.
More Null

If you have at sometime permitted a Boolean field to contain null your results can be erratic.
A left or right JOIN can occasionally present unhappy grief, too
It is almost always a far better habit to develop to reflexively exclude the items you know for certain you do not want than to blithely assume that an equals criteria will give you what you want when it comes to a Boolean field.  This is much more especially the case when you are checking for false, when a linked SQL Server table will, if you give it '= False' blithely provide you with all the false and null values.  Been there, lost hair and turned more grey, and done that.

Boolean logic is not intuitive and can also vary across systems.
True AND true = True
True AND False = False
True AND Null = Null  'ok that's fine
False AND Null = Null  'ok that's fine

True OR True = True
True OR False = True
True or Null = Null  'ok that's fine
False or Null = Null  'ok that's fine

Null <> Null 'some folks go WTF!
'and some system's logic actually go Null = Null , though that is wrong mathematically.

Don't get me wrong.
I am not a big fan of throwing the NOT operator all over the place.
Use that when there's no better way to define the logic.

But given that different systems have a different value for null, and that things like
Dim Something as String
Something = InputBox("True or False", "Choose!", True)
can play merry hell with your afternoon,
the dictum that when it comes to Booleans, you're better off to eliminate the things you don't want from the set, than to try and pick the ones you do, is pretty sensible.

= -1
= True

Both work in Access, but change the backend and they break
<> 0
Well, that works EVERYWHERE, and makes it worth knowing, and worth doing.

I've had enough occasions that testing for the positive has failed me that I much prefer exclusion of the negative as the alternative.
Nulls are a valid part of a set of data.  Some fields are required and therefore nulls are not allowed but for most fields, null is a valid value.  Where you get into trouble is when you allow ZLS to creep into your text fields.  Obviously, ZLS (or maybe not so obviously) is not valid in numeric type fields (including dates), just text type fields.

<> 0 is not the same as = -1 or = True.  You need a compound condition to deal with nulls if the field allows them which is something that continuously trips up newcomers.

Boolean logic is not intuitive and can also vary across systems.
Boolean logic is Boolean logic.  It does not vary across systems.  But Boolean logic does not consider null and the treatment of null does vary.  

Any condition that includes a null operand returns null in Access.

Checking for specific numeric values for True and False can get you into trouble since different RDBMS' implement them with different values but True and False are interpreted correctly in whatever query language you use.  So, in Access, True = -1 but in SQL Server True = 1 and since I have a number of applications that can swap ACE/SQL Server BE's by relinking, everything I write must work in both RDBMS'.  In some situations any non-zero, non-null value will return True.  So, True/False should be used to interrogate fields defined to be True/False and not used in random situations.  Jet/ACE allow null in Yes/No fields but SQL Server does not so if I have a case where I need the "unknown" state, I never use a BIT data type, I use an integer to avoid the issue.

I've had enough occasions that testing for the positive has failed me that I much prefer exclusion of the negative as the alternative.
Apparently I live in a different dimension.
Apparently I live in a different dimension.
Nope, just a different set of experiences -- which is ok
Jet/ACE allow null in Yes/No fields but SQL Server does not
You were saying...
table defBut
table dataI am not agreeing, with definitive proof yet to boot

It does not vary across systems.


I just do what I need to, to make sure things work the way I expect and want them to.
So do you, but we come from different experience sets, that's all.
We are having a semantics issue.  
Boolean algebra is the subarea of algebra in which the values of the variables are the truth values true and false, usually denoted 1 and 0 respectively
The papers on Boolean logic do not address the null.  They discuss true/false and the various relational operators.  The null is a relational database concept and each RDBMS may handle it differently.

In Access VBA, the only expression that will return true when examining a null field is the IsNull() function.  In Access SQL, the expression is "Is Null".
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.

All Courses

From novice to tech pro — start learning today.