Update query not working 1

[platypus71]

I have a table with the following fields:

FieldNameata Type
Emp#:Number
OrigCourseComplete:Yes/No
NewCourseComplete:Yes/No
DateAddedate/Time

I am trying to make an update query that will "check the box" in NewCourseComplete if it meets the necessary criteria. Here is what I have, but it does not "check the box". What do I need to change the value to to get the SET NewCourseComplete actually result in the checked box (ie Yes or true)?

UPDATE qryHPLTrainingHistory INNER JOIN tblHPLCompletion ON qryHPLTrainingHistory.[Emp#] = tblHPLCompletion.[Emp#] SET tblHPLCompletion.NewCourseComplete = Yes, tblHPLCompletion.[Emp#] = [qryHPLTrainingHistory].[Emp#]
WHERE (((qryHPLTrainingHistory.[CapStone Program]) Like "Complete") AND ((qryHPLTrainingHistory.[Effective Performance Management]) Like "Complete") AND ((qryHPLTrainingHistory.[Targeted Selection]) Like "Complete"));

 

[genomon]

>>UPDATE qryHPLTrainingHistory

You are trying to update a query, not the table.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[PHV]

what about this ?
UPDATE tblHPLCompletion
SET NewCourseComplete = True
WHERE [Emp#] In (SELECT [Emp#] FROM qryHPLTrainingHistory
WHERE [CapStone Program]='Complete' AND [Effective Performance Management]='Complete' AND [Targeted Selection]='Complete')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

Thanks PHV, that seemed to do the trick, though when I view the results, it doesn't show them as being set to 'True'. But when actually run, it does work.

Now I have an additional field I want to filter by, but am not sure how to append it to the above.

The additional information is:
If field [Band] is not equal to D, E, F, G, or H
Plus they need to have only completed [CapStone Program] and [Targeted Selection] to get credit.

So I need the above statement, but need to add an "OR" option at the end and include the new criteria from above.

 

[PHV]

I'm not sure I've really understood your issue.
I guess you want to replace this:
WHERE [CapStone Program]='Complete' AND [Effective Performance Management]='Complete' AND [Targeted Selection]='Complete')

with this:
WHERE [CapStone Program]='Complete' AND [Targeted Selection]='Complete'
AND ([Effective Performance Management]='Complete' OR Band Not In ('D','E','F','G','H')))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[platypus71]

Thanks, that was exactly what i needed. "Band Not In ('D','E','F','G','H')" was exactly the needed formatting. Thank you.