Can't get it work,"all Done"

[Emilio]

I decided to start a new message and maybe explain my
problem a little more.

You are right John Vinson,I have a Subform with three
controls, bound to a Table (or a Query) with three fields
(Scheduled, JobDate and Status).
In the Form I have a Check box that automatically will
switch to "ON" with the following expression:

=[JobSchedule Status Query subform1].[Form]![Status]
="Done"

And this will update my receivables so I can tell who
owes me money.

The problem is if I have say 2 lines (sometimes are 35):

Jones 08/15/03 Done
Jones 08/18/03 InstallerTold

(this all will show in a calendar for scheduling)
automatically this job will show as outstanding even if
it is not all completed.

Is there a way to create a query that checks ALL lines to
see if ALL is "Done"?

I received an answer from John Vinson:

SELECT * FROM yourtable
WHERE JobID = <the job you want to check>
AND Status <> "Done";

wich I thought will work perfectly ( and I am sure it
should) except I can't see how to use it in my case.
I tried using "Is Null" among other things but it got me,
can't get it to work.

Sorry if too confusing.
I hope someone can lend me a hand, this is driving me
insane.

Thanks,
Emilio

 

[John Spencer (MVP)]

Well, first of all you should stay in the same thread so people can easily get
what has been said and what solutions have been proposed.

What do you want returned? A list of all Jobs that are not finished; List of
all jobs; List of jobs and there most current status?

A simple list could be something like:

SELECT Table.Jobs
FROM Table
WHERE Exists
(SELECT *
FROM Table as Temp
Where Temp.Jobs = Table.Jobs
AND Temp.Status <> "Done")

 

[John Vinson]

What I need is a way to have my Check box turned "ON"
ONLY when all the entries (updated daily in the calendar)
are "Done" on the particular job.

hmmm... one way you could do this is by setting the Control Source of
the the checkbox to

=DCount("*", "[calendar table]", "[JobID] = " & [txtJobID] & " AND
Status = 'Done') = DCount("*", "[calendar table]", "[JobID] = " &
[txtJobID])

where txtJobID is the textbox on the form identifying the job. This
will count the records with status DONE and compare that value to the
count of all records; if the two numbers are equal the checkbox will
be TRUE, if not then FALSE.

 

[Emilio]

Thanks again John,
I think this should work
Except it tells me there is invalid syntax (I copied and
pasted your lines changing only the appropiate names)
I tried different things, but I am not good enough to see
what's wrong.

Thanks again,
Emilio

-----Original Message-----

What I need is a way to have my Check box turned "ON"
ONLY when all the entries (updated daily in the calendar)
are "Done" on the particular job.

hmmm... one way you could do this is by setting the Control Source of
the the checkbox to

=DCount("*", "[calendar table]", "[JobID] = " & [txtJobID] & " AND
Status = 'Done') = DCount("*", "[calendar table]", "[JobID] = " &
[txtJobID])

where txtJobID is the textbox on the form identifying the job. This
will count the records with status DONE and compare that value to the
count of all records; if the two numbers are equal the checkbox will
be TRUE, if not then FALSE.


.

 

[John Vinson]

Except it tells me there is invalid syntax (I copied and
pasted your lines changing only the appropiate names)
I tried different things, but I am not good enough to see
what's wrong.

Please post your actual expression, and indicate where you put it.

 

[Emilio]

=DCount("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName] & " AND Status = 'Done') = DCount
("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName])

I put the expression in the Check box named "Done" where
I used to have:
=[JobSchedule Status Query subform1].[Form]![Status]
="Done"

 

[Emilio]

Sorry, I think what you wanted to know is:
I put it in: Data-Control Source

 

[John Vinson]

=DCount("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName] & " AND Status = 'Done') = DCount
("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName])

Just a couple of syntax problems: you're missing a closing quote on
the first DCount; and if JobCustomerName is a Text field you need
quotemarks around it as well. Is JobCustomerName actually a person's
name? If so, you may want to reconsider! Names are NOT unique, and you
could very easily have two customers named Smith (or even Jim Smith).

If JobCustomerName is numeric:

=DCount("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName] & " AND Status = 'Done'") = DCount
("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName])

(closing " after 'Done')

If it's Text:

=DCount("*", "[JobSchedule]", "[Scheduled] = '" &
[JobCustomerName] & "' AND Status = 'Done'") = DCount
("*", "[JobSchedule]", "[Scheduled] = '" &
[JobCustomerName] & "'")

If JobCustomerName might contain an apostrophe (O'Brien frex) you need
to delimit with " rather than ': a " within a string is depicted using
two consecutive " marks.

=DCount("*", "[JobSchedule]", "[Scheduled] = """ &
[JobCustomerName] & """ AND Status = 'Done'") = DCount
("*", "[JobSchedule]", "[Scheduled] = """ &
[JobCustomerName] & """")

 

[Emilio]

Yes, now it works!
By the way "JobCustomerName" is unique made up of:
PO#-LastName-Installer-Days

One last question, how come it does not show like if it
is ticked?
Does it make any difference?

Thanks so much,
Emilio

-----Original Message-----

=DCount("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName] & " AND Status = 'Done') = DCount
("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName])

Just a couple of syntax problems: you're missing a closing quote on
the first DCount; and if JobCustomerName is a Text field you need
quotemarks around it as well. Is JobCustomerName actually a person's
name? If so, you may want to reconsider! Names are NOT unique, and you
could very easily have two customers named Smith (or even Jim Smith).

If JobCustomerName is numeric:

=DCount("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName] & " AND Status = 'Done'") = DCount
("*", "[JobSchedule]", "[Scheduled] = " &
[JobCustomerName])

(closing " after 'Done')

If it's Text:

=DCount("*", "[JobSchedule]", "[Scheduled] = '" &
[JobCustomerName] & "' AND Status = 'Done'") = DCount
("*", "[JobSchedule]", "[Scheduled] = '" &
[JobCustomerName] & "'")

If JobCustomerName might contain an apostrophe (O'Brien frex) you need
to delimit with " rather than ': a " within a string is depicted using
two consecutive " marks.

=DCount("*", "[JobSchedule]", "[Scheduled] = """ &
[JobCustomerName] & """ AND Status = 'Done'") = DCount
("*", "[JobSchedule]", "[Scheduled] = """ &
[JobCustomerName] & """")



.

 

[John Vinson]

Yes, now it works!
By the way "JobCustomerName" is unique made up of:
PO#-LastName-Installer-Days

Ow.

This is called an "Intelligent Key" and it's generally considered a
Very Bad Idea. You're storing data redundantly, it's hard to maintain,
if a customer changes her name you lose your link...

I would STRONGLY suggest putting a unique Index on these four fields,
adding an Autonumber JobCustomerID, and linking to it rather than to
this constructed key.

One last question, how come it does not show like if it
is ticked?

I'm sorry, I don't understand the question!