SQL for yes/no type data

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
VenkatagiriAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TrygveConnect With a Mentor Commented:
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
 
toesableCommented:
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
 
MarineCommented:
This works try it.
SELECT * FROM tblLinkNames WHERE P6 = Yes
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
BangerterCommented:
one other option is to use the numeric value for yes which would be -1 or no would be 0
0
 
toesableCommented:
Absolutely correct Trvqve.  toe
0
 
TrygveCommented:
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
 
VenkatagiriAuthor Commented:
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
 
TrygveCommented:
Thank you!
0
All Courses

From novice to tech pro — start learning today.