Test whether all results of a query = True

  • Thread starter Thread starter Andibevan
  • Start date Start date
A

Andibevan

The following parametized query returns a number of records. If my query
returns 10 records, I need to find out whether
tbl_CR_CR2Deliverables.Delivered = true for all 10 records.

Here's my query if that helps

SELECT tbl_CR_CR2Deliverables.CR, tbl_CR_CR2Deliverables.Delivered,
tbl_CR_CR2Deliverables.[File Name]
FROM tbl_CR_CR2Deliverables
WHERE (((tbl_CR_CR2Deliverables.CR) Like [CR:]));

TIA

Andi
 
Andibevan said:
The following parametized query returns a number of records. If my query
returns 10 records, I need to find out whether
tbl_CR_CR2Deliverables.Delivered = true for all 10 records.

Here's my query if that helps

SELECT tbl_CR_CR2Deliverables.CR, tbl_CR_CR2Deliverables.Delivered,
tbl_CR_CR2Deliverables.[File Name]
FROM tbl_CR_CR2Deliverables
WHERE (((tbl_CR_CR2Deliverables.CR) Like [CR:]));

TIA

Andi
Click to expand...
What I do for this sort of thing is create an iff statement which
returns a zero if it is what I am looking for, and a 1 if it is not.
Sum that field, and if you get zero, all records match.
All_Delivered:IIF(tbl_CR_CR2Deliverables.Delivered="TRUE",0,1)

Subtotal the query, summing All_Delivered. If it is zero, you are set.
Anything other than zero, that many records failed the test.
 
Back
Top