By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Checking the values of all records in a query

Seth Schrock
Expert 2.5K+
P: 2,941
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblDeviceTask.Completed
  2. FROM tblJob INNER JOIN 
  3. (tblTask INNER JOIN tblDeviceTask ON tblTask.TaskID = tblDeviceTask.TaskID) 
  4. ON tblJob.JobID = tblTask.JobID
  5. WHERE (((tblDeviceTask.[DeviceNumber])=[Forms]![frmJobCompletion]![sfrmDeviceJob_Completion]![DeviceNumber]) 
  6. 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?
Sep 12 '12 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 100+
P: 2,321
You could use an aggregate query and count the records where Completed is false. If the count>0 then the job is not complete.
Sep 12 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,941
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)?
Sep 12 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,941
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?
Sep 12 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,941
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?
Sep 12 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.