Simple query to show when items are not checked?

[mrwistles]

Ok here it is. As I said the most basic of basic.

As you can see from the only table the date of 01/09/2010 has a "tick" for all items apart from tasks 3, 5 and 9.

I have created a simple parameter query that asks for the date needed and then it should return all the tasks which were not completed for that day.

However I don't get the date and it returns ALL tasks as incomplete.

This is driving me spare, I can't see what I am doing wrong as this should be so easy.

Thanks for your help.

[weekend00]

Finally I understand what you mean "it returns all items as unchecked".

Actually, the query doesn't return any record . you saw the empty line is not a result, it's for adding a new record.
So, the result is right.

I asked you whether you want items with all 9 fields unchecked or any fields unchecked, and you did not give an answer. Looks like you want the latter one.

try this query:

SELECT Tbl_main.[Task 1], Tbl_main.[Task 2], Tbl_main.[Task 3], Tbl_main.[Task 4], Tbl_main.[Task 5], Tbl_main.[Task 6], Tbl_main.[Task 7], Tbl_main.[Task 8], Tbl_main.[Task 9], Tbl_main.Date
FROM Tbl_main
WHERE 0=( [task 1] AND [task 2] AND [task 3] AND [task 3] AND [task 4] AND [task 5] AND [task 6] AND [task 7] AND [task 8] AND [task 9]) AND Tbl_main.Date=[Please enter the required date];

[mrwistles]

Thanks that works but I only want to show the items which are not checked each time. The query is returning all items whether checked or not.

Also is there a way to not show the bottom line?

Picture enclosed: -

[weekend00]

I only want to show the items which are not checked each time

Yes, it can be done but maybe way like complicated. let me think for half hour, if I cann't get it, I give up.

I don't know how to get rid of the bottom empty line.

[weekend00]

All right, add "distinct" behind select gets rid of the bottom line, and make it unupdatable as well.

SELECT distinct Tbl_main.[Task 1], Tbl_main.[Task 2], Tbl_main.[Task 3], Tbl_main.[Task 4], Tbl_main.[Task 5], Tbl_main.[Task 6], Tbl_main.[Task 7], Tbl_main.[Task 8], Tbl_main.[Task 9], Tbl_main.Date
FROM Tbl_main
WHERE 0=( [task 1] AND [task 2] AND [task 3] AND [task 3] AND [task 4] AND [task 5] AND [task 6] AND [task 7] AND [task 8] AND [task 9]) AND Tbl_main.Date=[Please enter the required date];

[weekend00]

I only want to show the items which are not checked each time

if you modify you table structure as following, it will be much easier:
Task Date Completed
1 01/09/2010 YES
2 01/09/2010 YES
3 01/09/2010 NO
4 01/09/2010 YES
5 01/09/2010 YES
6 01/09/2010 NO
7 01/09/2010 YES
8 01/09/2010 YES
9 01/09/2010 YES
1 02/09/2010 YES
2 02/09/2010 NO
3 02/09/2010 YES
4 02/09/2010 YES
5 02/09/2010 NO
6 02/09/2010 YES
7 02/09/2010 YES
8 02/09/2010 YES
9 02/09/2010 YES

[mrwistles]

Sorry weekend I do not follow the above line. The code change removed the line though. Thanks a lot.

[weekend00]

that's all right.

[mrwistles]

So in regards to the table structure you recommend that I do not use check boxes and merely Yes or No as a text field?

[weekend00]

That's not the point. checkbox is yes/no type, YES/NO is also yesno type. you may keep "completed" as checkbox.

What i suggested is you take down task 1 to task 9 and put them vertically.

[mrwistles]

What I want to happen at the moment is that when a button is clicked for new record all of the fields appear again to be checked for a different day.

If I did it vertically this would not be possible.

[weekend00]

You are right. but it's too complicated for me to pick only the unchecked fields.

[mrwistles]

You are right. but it's too complicated for me to pick only the unchecked fields.

Thanks a lot for your help.

[weekend00]

I am marking this issue as solved, if you want help for pick uncheck fields, please post a new issue.