Query for any "False" item in record 1

[milcman]

I have a table that has a bunch of check box fields. I want to be able to query for any records that DO NOT have all check boxes checked. Any suggestions?

Clint Galliano
Halliburton Energy Services
BAROID PSL
"Done ONCE, Done RIGHT!"

 

[Golom]

How practical this is depends on how many fields constitutes "a bunch"

   Select Field
   From tbl
   Where NOT (F1 AND F2 AND F3 AND F4 AND ... )

 

[milcman]

I figured out what I needed to do. It's along the order of +/-18 check box fields. I was doing the query in design view and decided to look at the SQL view. There I saw that I could change the "AND" to "OR" and have it work like I wanted.


Thanks!!!

Clint Galliano
Halliburton Energy Services
BAROID PSL
"Done ONCE, Done RIGHT!"

 

[milcman]

Here is the SQL that I used:


SELECT tblWells.atoWellID, tblWells.strWellTechID, tblWells.OperatorName, tblWells.intQuoteNumber, tblWells.strWellName, tblWells.strArea, tblWells.strFieldBlock, tblWells.RigName, tblWells.dtmSpudDate, tblWells.dtmWellEndDate, tblWells.ysnOffsetData, tblWells.dtmOffsetData, tblWells.ysnDailyTracking, tblWells.dtmDailyTracking, tblWells.ysnAFE, tblWells.dtmAFE, tblWells.ysnWellProgram, tblWells.dtmWellProgram, tblWells.ysnPreBlueprint, tblWells.dtmPreBlueprint, tblWells.ysnWellAwarded, tblWells.dtmWellAwarded, tblWells.ysnEndWellX_Fer, tblWells.dtmEndWellX_Fer, tblWells.ysnX_FerToTech, tblWells.dtmX_FerToTech, tblWells.ysnInvoiceDelivered, tblWells.dtmInvoiceDelivered, tblWells.ysnEJCS, tblWells.dtmEJCS, tblWells.ysnKPI, tblWells.dtmKPI, tblWells.ysnBenchmark, tblWells.dtmBenchmark, tblWells.ysnPostBlueprint, tblWells.dtmPostBlueprint, tblWells.ysnExecutiveSummary, tblWells.dtmExecSumm, tblWells.ysnCDRecap, tblWells.dtmCDRecap, tblWells.ysnPWAToClient, tblWells.dtmPWAToClient, tblWells.ysnEVC, tblWells.dtmEVC, tblWells.ysnWS2kServer, tblWells.dtmWS2kServer, tblWells.[ysnX-FerPerformance], tblWells.[dtmX-FerPerformance]
FROM tblWells
WHERE (((tblWells.ysnInvoiceDelivered)=False) OR ((tblWells.ysnEJCS)=False) OR ((tblWells.ysnKPI)=False) OR ((tblWells.ysnBenchmark)=False) OR ((tblWells.ysnPostBlueprint)=False) OR ((tblWells.ysnExecutiveSummary)=False) OR ((tblWells.ysnCDRecap)=False) OR ((tblWells.ysnPWAToClient)=False) OR ((tblWells.ysnEVC)=False) OR ((tblWells.ysnWS2kServer)=False) OR ((tblWells.[ysnX-FerPerformance])=False));

Clint Galliano
Halliburton Energy Services
BAROID PSL
"Done ONCE, Done RIGHT!"

 

[CosmoKramer]

Clint,

In your original post you said "I want to be able to query for any records that DO NOT have all check boxes checked

If you only want to return records where all check boxes are unchecked you should probably change your ORs to ANDs....

Hoc nomen meum verum non est.

 

[milcman]

Cosmo,

"OR" is the correct operand. This returns a list of projects where if any component of it is not checked off, it will show up in the list.

Clint Galliano
Halliburton Energy Services
BAROID PSL
"Done ONCE, Done RIGHT!"

 

[CosmoKramer]

I guess I was confused by your conflicting
Query for any "False" item in record
title and
I want to be able to query for any records that DO NOT have all check boxes checked
first post....

Hoc nomen meum verum non est.

 

[JonFer]

You can more easily do this by adding the Checkbox fields together and setting the criteria to >-11 (if there are 11 checkboxes). Then only one comparison is done. For example:

Where (checkbox1 + checkbox2) > -2

 

[lespaul]

What a great idea JonFer, I'll have to remember that! Have a star on me!

Leslie