Setting Values to Null or False Using a Update Query

[Chris Belcher]

I'm trying to set a couple of values in a master table based on the
detail table. Basically, If there are any tasks that are not complete in
the detail table I want the Master tables complete flag and complete set
to False and Null respectively. I set up a query to find the records to
update and then planned on using a update query to set the values. The
following does not work. Given the "Operation must use an updateable
query" response when running it, I assume that it is looking to get the
value from another field not a specifically set value.


UPDATE CDQstep1A INNER JOIN [AI Master] ON CDQstep1A.[AI Key] = [AI
Master].[AI Key] SET [AI Master].Complete = False, [AI
Master].CompleteDate = Null
WHERE ((([AI Master].Complete)=False) AND (([AI Master].CompleteDate) Is
Not Null) AND (([MasterAIKeyCount]-[CompDtCountDetail])<>0)) OR ((([AI
Master].Complete)=True) AND (([AI Master].CompleteDate) Is Null) AND
(([MasterAIKeyCount]-[CompDtCountDetail])<>0)) OR ((([AI
Master].Complete)=True) AND (([AI Master].CompleteDate) Is Not Null) AND
(([MasterAIKeyCount]-[CompDtCountDetail])<>0));


Could someone point me in the right direction?

Thanks,
Chris Belcher

 

[John Vinson]

I'm trying to set a couple of values in a master table based on the
detail table. Basically, If there are any tasks that are not complete in
the detail table I want the Master tables complete flag and complete set
to False and Null respectively. I set up a query to find the records to
update and then planned on using a update query to set the values. The
following does not work. Given the "Operation must use an updateable
query" response when running it, I assume that it is looking to get the
value from another field not a specifically set value.


UPDATE CDQstep1A INNER JOIN [AI Master] ON CDQstep1A.[AI Key] = [AI
Master].[AI Key] SET [AI Master].Complete = False, [AI
Master].CompleteDate = Null
WHERE ((([AI Master].Complete)=False) AND (([AI Master].CompleteDate) Is
Not Null) AND (([MasterAIKeyCount]-[CompDtCountDetail])<>0)) OR ((([AI
Master].Complete)=True) AND (([AI Master].CompleteDate) Is Null) AND
(([MasterAIKeyCount]-[CompDtCountDetail])<>0)) OR ((([AI
Master].Complete)=True) AND (([AI Master].CompleteDate) Is Not Null) AND
(([MasterAIKeyCount]-[CompDtCountDetail])<>0));

Is CDQStep1A a Totals query? If so, you won't be able to do any Update
query joined to it - no Totals query, nor any query containing a
Totals query, is ever updateable. Just a built in Access restriction.

 

[Chris Belcher]

Thanks John. I'll have to attack the problem another way.

I'm trying to set a couple of values in a master table based on the
detail table. Basically, If there are any tasks that are not complete in
the detail table I want the Master tables complete flag and complete set
to False and Null respectively. I set up a query to find the records to
update and then planned on using a update query to set the values. The
following does not work. Given the "Operation must use an updateable
query" response when running it, I assume that it is looking to get the
value from another field not a specifically set value.


UPDATE CDQstep1A INNER JOIN [AI Master] ON CDQstep1A.[AI Key] = [AI
Master].[AI Key] SET [AI Master].Complete = False, [AI
Master].CompleteDate = Null
WHERE ((([AI Master].Complete)=False) AND (([AI Master].CompleteDate) Is
Not Null) AND (([MasterAIKeyCount]-[CompDtCountDetail])<>0)) OR ((([AI
Master].Complete)=True) AND (([AI Master].CompleteDate) Is Null) AND
(([MasterAIKeyCount]-[CompDtCountDetail])<>0)) OR ((([AI
Master].Complete)=True) AND (([AI Master].CompleteDate) Is Not Null) AND
(([MasterAIKeyCount]-[CompDtCountDetail])<>0));

Is CDQStep1A a Totals query? If so, you won't be able to do any Update
query joined to it - no Totals query, nor any query containing a
Totals query, is ever updateable. Just a built in Access restriction.