Checking the values of all records in a query

[Seth Schrock]

I have a query that returns a check box that tells me if a task has been completed. What I'm trying to do is have some code that will mark the job completed once all of the tasks are completed. My problem is that I don't know how to check if all the check boxes are true in the query. Here is the query:

SELECT tblDeviceTask.Completed
FROM tblJob INNER JOIN 
(tblTask INNER JOIN tblDeviceTask ON tblTask.TaskID = tblDeviceTask.TaskID) 
ON tblJob.JobID = tblTask.JobID
WHERE (((tblDeviceTask.[DeviceNumber])=[Forms]![frmJobCompletion]![sfrmDeviceJob_Completion]![DeviceNumber]) 
AND ((tblJob.JobID)=[Forms]![frmJobCompletion]![cboJobName]));
 

Is it possible to just test if the query is True? Or do I have to loop through all of the records testing the value each time and if so, how?

[TheSmileyCoder]

You could use an aggregate query and count the records where Completed is false. If the count>0 then the job is not complete.

[Seth Schrock]

I just tried using the Count when three records were false and the count was zero. Is it counting the values of the checkbox (ie. 0 and 1)?

[Seth Schrock]

I just tried the count on the query without having the false criteria. There were 8 records returned: 5 checked and 3 unchecked. The count was 5. Should I compare the Count value with the record count and if the same, then job is complete?

[Seth Schrock]

I figured out a way to do it. I reset the criteria to have Completed = False (if all the tasks are completed, then the query will be empty). I then did a DCount in my VBA function which counts the records in the query. If the value is 0, then the job is completed. This does work, but would it be better to do it another way?