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? |
| arcanedevice Friday, October 09, 2009 5:19 AM |
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"
- Marked As Answer byMartin Xie - MSFTMSFT, ModeratorThursday, October 15, 2009 3:47 AM
-
|
| jgalley 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! - Edited bySylva Monday, October 12, 2009 9:09 PMEdit
- Marked As Answer byMartin Xie - MSFTMSFT, ModeratorThursday, October 15, 2009 3:47 AM
-
|
| Sylva 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."- Marked As Answer byMartin Xie - MSFTMSFT, ModeratorThursday, October 15, 2009 3:48 AM
-
|
| Rudedog2 Monday, October 12, 2009 11:01 PM |
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." |
| Rudedog2 Friday, October 09, 2009 12:56 PM |
In Access,
try
"SELECT * From table WHERE CheckBox1 = -1"
or
"SELECT * From table WHERE CheckBox1 = True"
- Proposed As Answer byPaul P Clement IVMVPMonday, October 12, 2009 1:18 PM
-
|
| jgalley Friday, October 09, 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! |
| Malange Friday, October 09, 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 |
| Renee Culver 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." |
| Rudedog2 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! |
| Sylva 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"
- Marked As Answer byMartin Xie - MSFTMSFT, ModeratorThursday, October 15, 2009 3:47 AM
-
|
| jgalley 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." |
| Rudedog2 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! |
| Sylva 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) |
| Paul P Clement IV 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." |
| Rudedog2 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) |
| Paul P Clement IV 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." |
| Rudedog2 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! - Edited bySylva Monday, October 12, 2009 9:09 PMEdit
- Marked As Answer byMartin Xie - MSFTMSFT, ModeratorThursday, October 15, 2009 3:47 AM
-
|
| Sylva 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. |
| jgalley 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! |
| Sylva 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) |
| Paul P Clement IV 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."- Marked As Answer byMartin Xie - MSFTMSFT, ModeratorThursday, October 15, 2009 3:48 AM
-
|
| Rudedog2 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. |
| Martin Xie - MSFT Thursday, October 15, 2009 3:54 AM |
index > Visual Basic Express Edition > Search Access Database for Yes / No value