Return only false values in query

[dbowens]

I have a table that has a list of tasks and checkboxes attached to them to be checked once the task is completed. I need to run a query on the table that will only bring me back the tasks with a completion that is false.

Everything that I read online indicates that this is a difficult task for access. Maybe I can accomplish this in SQL view instead of design? If I put false is all of the yes/no fields, the query brings back nothing. All help is appreciated!

 

[dbowens]

Thanks. If I place "false" in all of the criteria fields, access brings back no values in the query. Every field that is selected in the query would have to be equal to false. Any other suggestions?

 

[dbowens]

Perfect. Thanks man. Learning more everyday.

 

[dbowens]

Still having issues with this. I need to only see where the values are false. If I use the structure you suggested, the query brings back all yes/no fields regardless of whether they are true or false.

 

[dbowens]

This is the SQL:
SELECT [Election Night Returns].[Confirmed PCMCIA Card], [Election Night Returns].[Confirmed IVO Media Card], [Election Night Returns].[Confirmed IVO Zero Results Tape], [Election Night Returns].[Confirmed IVO Results Tape], [Election Night Returns].[Confirmed M100 Zero Results Tape], [Election Night Returns].[Confirmed M100 Results Tape], [Election Night Returns].[Confirmed Flash Drives], [Election Night Returns].[Confirmed Machine Keys], [Election Night Returns].[Confirmed Facility Keys], [Election Night Returns].[Confirmed Master PEB]
FROM [Election Night Returns]
WHERE ((([Election Night Returns].[Confirmed PCMCIA Card])=False) AND (([Election Night Returns].[Confirmed IVO Media Card])=False) AND (([Election Night Returns].[Confirmed IVO Zero Results Tape])=False) AND (([Election Night Returns].[Confirmed IVO Results Tape])=False) AND (([Election Night Returns].[Confirmed M100 Zero Results Tape])=False) AND (([Election Night Returns].[Confirmed M100 Results Tape])=False) AND (([Election Night Returns].[Confirmed Flash Drives])=False) AND (([Election Night Returns].[Confirmed Machine Keys])=False) AND (([Election Night Returns].[Confirmed Facility Keys])=False));

 

[vbaInet]

More legible (AccessBlaster):

SELECT 	[Confirmed PCMCIA Card], 
	[Confirmed IVO Media Card], 
	[Confirmed IVO Zero Results Tape], 
	[Confirmed IVO Results Tape], 
	[Confirmed M100 Zero Results Tape], 
	[Confirmed M100 Results Tape], 
	[Confirmed Flash Drives], 
	[Confirmed Machine Keys], 
	[Confirmed Facility Keys], 
	[Confirmed Master PEB]
FROM   	[Election Night Returns]
WHERE 	    ([Confirmed PCMCIA Card]=False) 
       	AND ([Confirmed IVO Media Card]=False) 
        AND ([Confirmed IVO Zero Results Tape]=False) 
       	AND ([Confirmed IVO Results Tape]=False) 
       	AND ([Confirmed M100 Zero Results Tape]=False) 
       	AND ([Confirmed M100 Results Tape]=False) 
       	AND ([Confirmed Flash Drives]=False) 
       	AND ([Confirmed Machine Keys]=False) 
       	AND ([Confirmed Facility Keys]=False)

 

[RainLover]

You have 9 (Nine) Fields where you could apply your condition of "False"

What if 2 Fields are "False" and 7 are "True"
What if 4 Fields are "False" and 5 are "True"
What if 7 Fields are "False" and 2 are "True"

Does this make a difference or are you looking for a record where and number of fields could be false.

If only 2 Fields are false do you still want to show all 7 fields.

 

[jdraw]

Perhaps you could tell us more about what this means exactly'

I need to run a query on the table that will only bring me back the tasks with a completion that is false.

What is COMPLETION?

 

[RainLover]

This worked for me. I tested it on a new Database. I would suggest that if you decide to use it yourself then test it toughly.

[SIZE=2]SELECT tblContacts.FirstName, tblContacts.LastName, tblContacts.A, tblContacts.B,tblContacts.C
FROM tblContacts
WHERE (((tblContacts.A)=0) AND ((tblContacts.B)=0) AND ((tblContacts.C)=0)) 
OR (((tblContacts.B)=0) AND ((tblContacts.C)=0)) 
OR (((tblContacts.C)=0));[/SIZE]

 

[RainLover]

I had another look at my own work and now believe it is not quite right.

But I shall leave it up on site for comparison.

If we need every possibility that 9 Fields would create (81 maybe) then I do not think SQL will handle it.

It will be interesting to see the final result on this one.

 

[dbowens]

Thanks for all the help. I will test that out rainlover. To answer your question Jdraw, I have a form that has 9 different tasks that need to be verified. The tenth field is a complete field. The complete field will only populate as true if all other task fields are populated as true. If all are not true then the completed field will not populate.

When running a query on that data which is flowing to my main table, I want to only see records where tasks are false and the completed field is false. Currently when I run the query, everything returns for each record, not just those that are false.

I hope this helps. If not I can upload the database.

 

[dbowens]

Below is the code from SQL view and it is not returning any results.

SELECT Distinct ElectionNightReturns.Precinct, ElectionNightReturns.ConfirmedPCMCIACard, ElectionNightReturns.ConfirmedIVOMediaCard, ElectionNightReturns.ConfirmedIVOZeroResultsTape, ElectionNightReturns.ConfirmedIVOResultsTape, ElectionNightReturns.ConfirmedM100ZeroResultsTape, ElectionNightReturns.ConfirmedM100ResultsTape, ElectionNightReturns.ConfirmedFlashDrives, ElectionNightReturns.ConfirmedMasterPEB, ElectionNightReturns.Completed
FROM ElectionNightReturns
WHERE (((ElectionNightReturns.ConfirmedPCMCIACard)=False) AND ((ElectionNightReturns.ConfirmedIVOMediaCard)=False) AND ((ElectionNightReturns.ConfirmedIVOZeroResultsTape)=False) AND ((ElectionNightReturns.ConfirmedIVOResultsTape)=False) AND ((ElectionNightReturns.ConfirmedM100ZeroResultsTape)=False) AND ((ElectionNightReturns.ConfirmedM100ResultsTape)=False) AND ((ElectionNightReturns.ConfirmedFlashDrives)=False) AND ((ElectionNightReturns.ConfirmedMasterPEB)=False));

 

[dbowens]

There is a combination of checked and unchecked. I have attached a copy of the database if you would like to view it.

 

[RainLover]

I have just had another play with this

I still do not understand which records need to be opened and I did ask an appropriate question in post #10 but that was not answered.

I think I can write the solution once I fine tune the requirements.

I am suggesting writing a UDF. A User Defined Function.

I will bail out here as you and Access Blaster appear to be on the way to solving the problem.

Give me a nudge if you want my input.

Thank You

 

[vbaInet]

[Confirmed PCMCIA Card], 
[Confirmed IVO Media Card], 
[Confirmed IVO Zero Results Tape], 
[Confirmed IVO Results Tape], 
[Confirmed M100 Zero Results Tape], 
[Confirmed M100 Results Tape], 
[Confirmed Flash Drives], 
[Confirmed Machine Keys], 
[Confirmed Facility Keys], 
[Confirmed Master PEB]

I'm only just joining this conversation (even though I posted the above earlier).

What we can see here is a data design flaw. These checkboxes are individual fields instead of individual records so this needs to be ironed out.

If you still want to go with your bad design then Use the OR predicate and put False under each field.