?
Solved

SQL for yes/no type data

Posted on 2000-02-20
8
Medium Priority
?
682 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
[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
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 12

Accepted Solution

by:
Trygve earned 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

800 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