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
Solved

SQL for yes/no type data

Posted on 2000-02-20
8
665 Views
Last Modified: 2012-08-14
I have an access table with yes/no data type.  Each of the fields in the table has a box, which can be checked (presumably meaning "yes") or unchecked (i. e. empty presumably meaning "no").  An attempt to query this data table produced the following error.  I guess there is another way to form queries with this data type.  Please help.

SQL Statement:
SELECT * FROM tblLinkNames WHERE P6 = 'Yes'

Error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

The request properties can not be supported by this ODBC Driver.

/infopages/search_results.asp, line 44
0
Comment
Question by:Venkatagiri
8 Comments
 

Expert Comment

by:toesable
ID: 2541160
SELECT * FROM tblLinkNames WHERE [P6] = Yes

I believe, and I could be wrong if you really are going through ODBC, that the only problem with your SQL is that you have apostrophies around yes.  The above works in my database.

Maybe someone else will have an idea if this doesn't work.
0
 
LVL 6

Expert Comment

by:Marine
ID: 2541164
This works try it.
SELECT * FROM tblLinkNames WHERE P6 = Yes
0
 
LVL 2

Expert Comment

by:Bangerter
ID: 2541268
one other option is to use the numeric value for yes which would be -1 or no would be 0
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 12

Accepted Solution

by:
Trygve earned 100 total points
ID: 2541324
Bangerter: Since this is ASP: True = 1, False = 0

The best way to search for boolean values (and keeping the expression compatible with Access AND ASP environment) is to use 0 for False and <> 0 for True

Searching for True:
SELECT * FROM tblLinkNames WHERE [P6] <> 0

Searching for False:
SELECT * FROM tblLinkNames WHERE [P6] = 0

Also: Yes is never an option since it is just one of the possible formats for showing boolean fields in forms and reports (it is supported by Access though). The "textual" alternatives are True and False.

SELECT * FROM tblLinkNames WHERE [P6] = True
0
 

Expert Comment

by:toesable
ID: 2541333
Absolutely correct Trvqve.  toe
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2541361
Actually the name is Trygve, but the underline used for URLs here at EE makes it easy to do mistakes. I have posted a suggestion to change the underline format so that it does not "messes with our logins" :-)) http://www.experts-exchange.com/bin/Q.10298381
0
 

Author Comment

by:Venkatagiri
ID: 2543814
Thanks for all your comments.  The solution offered by Trygve was the most general and it worked for me.  Some of the other solutions offered would also probably work in this particular situation.  Thanks for your help.  I have allocated the points to Trygve.
0
 
LVL 12

Expert Comment

by:Trygve
ID: 2545047
Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

828 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