locked
Search Access Database for Yes / No value RRS feed

  • Question

  • I'm wondering if anyone can assist me with searching an access database for a Yes/No field.  What I'm trying to do is search the database and return all records where the field has a 'YES' response.  My code is along the lines of:

    "SELECT * From table WHERE CheckBox1 = 1"

    but everytime that I try the search function I get me error message saying that no value is located, even though there is currently only 1 record in the table and I know it meets the criteria...

    Any ideas where I might be going wrong?

    Friday, October 9, 2009 5:19 AM

Answers

  • The answer is above.

    In Access SQL a "checked" yes/no value does not equal 1, it equals -1

    try

    "SELECT * From table WHERE CheckBox1 = -1"

    or

    "SELECT * From table WHERE CheckBox1 = True"

    Saturday, October 10, 2009 9:45 PM
  • Either a value of -1 or True as jgalley indicated. Both evaluate to the same thing when using Jet SQL and is the "true" value that is stored in an Access Yes/No column.
    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    What I mean is that CheckBox1 = -1 can't be a proper way of doing this. If anything, it should be CheckBox1.CHECKED = -1. But then, the info from the OP is too scanty to provide any meaningful answer.


    EDIT:

    Except you mean that CheckBox1 is the name of a Column in his Table which I seriously doubt.



    Only performance counts!
    Monday, October 12, 2009 9:06 PM
  • The problem as I see is multi-faceted.

    A table named "table".
    A column named "CheckBox1".
    An "error message", not the word exception as Paul noted to me, that doesn't quite fit the few facts we have.
    Mark the best replies as answers. "Fooling computers since 1971."
    Monday, October 12, 2009 11:01 PM

All replies

  • Post the method where you perform this action.
    Identify the line where you get the error message, and the exact content of the error.

    A query that returns no results is not a condition that causes an exception. 
    Simply no matches were found.  Happens all the time, not cause for an exception
    Verify the name of your data table, and the field name within it, that you use in your SELECT string.



    Mark the best replies as answers. "Fooling computers since 1971."
    Friday, October 9, 2009 12:56 PM
  • In Access,

    try

    "SELECT * From table WHERE CheckBox1 = -1"

    or

    "SELECT * From table WHERE CheckBox1 = True"

    Friday, October 9, 2009 4:58 PM
  • I'm wondering if anyone can assist me with searching an access database for a Yes/No field.  What I'm trying to do is search the database and return all records where the field has a 'YES' response.  My code is along the lines of:

    "SELECT * From table WHERE CheckBox1 = 1"

    but everytime that I try the search function I get me error message saying that no value is located, even though there is currently only 1 record in the table and I know it meets the criteria...

    Any ideas where I might be going wrong?


     

    Using con As New OleDbConnection(connectionString)

     

    Dim name As New OleDbCommand("Select * From BOOK_LOANED Where Payed=1", con)

     

    'name.Parameters.AddWithValue("@Name", Me.CheckBox1.CheckState = False)

    con.Open()

     

    Dim reader As OleDbDataReader = name.ExecuteReader()

     

    With reader

     

    If .HasRows Then

    .Read()

     

    Dim binn As New BindingSource

    binn.DataSource = reader

     

    Me.DataGridView1.DataSource = binn

     

    End If

     

    End With

     

    End Using


    Don't judge me, just Upgrade me. Thanks!
    Friday, October 9, 2009 7:04 PM
  • Reading this. I'd say the same that you did, Rudedog2. I would wonder if he was doing it right (making the call correctly).
    Renee
    Saturday, October 10, 2009 4:30 PM
  • I'm wondering if anyone can assist me with searching an access database for a Yes/No field.  What I'm trying to do is search the database and return all records where the field has a 'YES' response.  My code is along the lines of:

    "SELECT * From table WHERE CheckBox1 = 1"

    but everytime that I try the search function I get me error message saying that no value is located, even though there is currently only 1 record in the table and I know it meets the criteria...

    Any ideas where I might be going wrong?



    What is the name of your data table?  "table"?
    Mark the best replies as answers. "Fooling computers since 1971."
    Saturday, October 10, 2009 5:13 PM
  • You should provide the name of your Table and the names of the columns. Indicate the column that has YES/NO data type. The info will assist in answering your question.
    Only performance counts!
    Saturday, October 10, 2009 9:43 PM
  • The answer is above.

    In Access SQL a "checked" yes/no value does not equal 1, it equals -1

    try

    "SELECT * From table WHERE CheckBox1 = -1"

    or

    "SELECT * From table WHERE CheckBox1 = True"

    Saturday, October 10, 2009 9:45 PM
  • The use of the name "table" for a data table is raising red flags for me. 
    Default table names are created sequentially in Access; i.e., table1, table2, table3, etc.


    Mark the best replies as answers. "Fooling computers since 1971."
    Saturday, October 10, 2009 10:30 PM

  • @Paul P Clement,

    Are you saying that CheckBox1 = -1 is in order, by any chance; or am I missing something?
    Only performance counts!
    Monday, October 12, 2009 7:19 PM
  • Either a value of -1 or True as jgalley indicated. Both evaluate to the same thing when using Jet SQL and is the "true" value that is stored in an Access Yes/No column.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, October 12, 2009 7:30 PM
  • I'm wondering if anyone can assist me with searching an access database for a Yes/No field.  What I'm trying to do is search the database and return all records where the field has a 'YES' response.  My code is along the lines of:

    "SELECT * From table WHERE CheckBox1 = 1"

    but everytime that I try the search function I get me error message saying that no value is located, even though there is currently only 1 record in the table and I know it meets the criteria...

    Any ideas where I might be going wrong?



    The OP reports an error message the says "no value is located". 
    Not syntax error.
    Not a null/nothing object error.
    "No value is located."

    Does that make sense?  It doesn't make sense to me.
    I don't think the problem is with the filter part of it.
    If that part of the string were wrong, that is not the message you should get.
    I don't think the table is being located.
    Mark the best replies as answers. "Fooling computers since 1971."
    Monday, October 12, 2009 7:42 PM
  • That could be the message that he is displaying in his application, which would make sense to me. A YES/NO (Boolean) column will never contain a value of one.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, October 12, 2009 7:48 PM
  • Okay, I'll concede that. 
    That's why I asked for the OP for exact content of the message and the exact name of his table.
    This seems to be a case of more than one problem.

    Mark the best replies as answers. "Fooling computers since 1971."
    Monday, October 12, 2009 7:51 PM
  • Either a value of -1 or True as jgalley indicated. Both evaluate to the same thing when using Jet SQL and is the "true" value that is stored in an Access Yes/No column.
    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    What I mean is that CheckBox1 = -1 can't be a proper way of doing this. If anything, it should be CheckBox1.CHECKED = -1. But then, the info from the OP is too scanty to provide any meaningful answer.


    EDIT:

    Except you mean that CheckBox1 is the name of a Column in his Table which I seriously doubt.



    Only performance counts!
    Monday, October 12, 2009 9:06 PM
  • The problem he is having is with MS Access SQL grammar.  He is issuing a SQL statement that he is expecting to return (at least) one row.  It is not returning any rows (and never will).

    SELECT * FROM employee WHERE isEmployed = 1

    Will never return any rows in Access.
    Monday, October 12, 2009 9:31 PM
  • Are you saying that isEmployed is the name of his YES/NO column?

    This is a very simple problem. If he supplies his Table definitions (column names and data types) he will receive the right answer.
    Only performance counts!
    Monday, October 12, 2009 9:35 PM
  • Either a value of -1 or True as jgalley indicated. Both evaluate to the same thing when using Jet SQL and is the "true" value that is stored in an Access Yes/No column.
    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    What I mean is that CheckBox1 = -1 can't be a proper way of doing this. If anything, it should be CheckBox1.CHECKED = -1. But then, the info from the OP is too scanty to provide any meaningful answer.


    EDIT:

    Except you mean that CheckBox1 is the name of a Column in his Table which I seriously doubt.



    Only performance counts!

    Yes, based upon the code he posted CheckBox1 would have to be a column name. The database engine would not be able to resolve the value of CheckBox1 if it was the name of a control on a Form, and execution of the statement would result in a parameter exception.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, October 12, 2009 9:43 PM
  • The problem as I see is multi-faceted.

    A table named "table".
    A column named "CheckBox1".
    An "error message", not the word exception as Paul noted to me, that doesn't quite fit the few facts we have.
    Mark the best replies as answers. "Fooling computers since 1971."
    Monday, October 12, 2009 11:01 PM
  • I'm glad to see so active discussion here :)
    Thank you All for your friendly help and support.

    Hi arcanedevice,

    Welcome to MSDN forums!

    You can select the right solution according to your specific question.
    If we misunderstood you, please kindly elaborate your question, you will be able to get quicker and better responses.


    Best regards,
    Martin Xie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 15, 2009 3:54 AM
  • Thanks to all who have responded to my original post.  I apologise for the delay in responding to you all but unfortunately I posted this at the end of one day and my wife went into labour the very next day, so have been absent for some time from my work and these forums.

    To answer some of your questions, my posting was obviously not my exact code but I've been asked by my employer not to provide actual database names for confidentiality purposes with this particular project (silly I know, but what can I do!), hence why I've used table and checkbox instead.  And yes, I probably should have specified that my 'error' message is actually a msgbox designed to appear where NIL matching records are found.  And the final flaw in my processing was that yes, my code above was incorrect and I should have had -1 instead of 1, a fault in trying to get my message up there too quickly.

    Had I had the extra day to check my code and an older test version of the database I probably would have deleted this posting because after a few hours searching through today I've identified that the error was actually in the record within the database that I was expecting to appear - the Y/N box wasn't selected for the field I was searching!  Unfortunately because I'm not yet back in the office and don't have access to the database I can't check it, but hopefully I'll have the correct response in the next debug.

    However, it has been interesting to read through your combined responses and discussions and I'm sure they'll help me improve my code as well as future postings...

    Wednesday, October 28, 2009 1:59 AM