Solved

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

Posted on 2014-12-22
14
305 Views
Last Modified: 2015-01-31
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?
TestDB.accdb
0
Comment
Question by:cssc1
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 100 total points
ID: 40514044
Microsoft usually records true as non-zero and false as zero.  Try this

SELECT * FROM tbl WHERE ckField <> 0;
0
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept earned 100 total points
ID: 40514050
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)
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 40514060
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.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 100 total points
ID: 40514130
Not disagreeing with the logic of @PatHartman's comment

BUT
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 :)
0
 

Author Comment

by:cssc1
ID: 40514279
I am getting an error with:
 SELECT * FROM tbl WHERE ckField =TRUE

See attached
error-1.jpg
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40514498
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.
0
 

Author Comment

by:cssc1
ID: 40514815
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];
SELECT * FROM tbl WHERE ckField =TRUE
error-db-image.jpg
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

Accepted Solution

by:
PatHartman earned 200 total points
ID: 40514843
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;
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40515072
Like thischange
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40515479
@ 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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40515585
NULL
NULL
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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40515615
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40515669
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.
http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#GUC-TRANSFORM-NULL-EQUALS
http://msdn.microsoft.com/en-us/library/ms188048.aspx

So...

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.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40519101
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".
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

18 Experts available now in Live!

Get 1:1 Help Now