AOB
Registered User.
- Local time
- Today, 23:46
- Joined
- Sep 26, 2012
- Messages
- 637
I am trying to UPDATE a table based on the result/value for the corresponding record in a separate query.
This is the basic syntax of the query :
So basically, look for the ID in the query, if the corresponding value for the record in question is 0, or the record is not present at all, set the LockID to 0, otherwise (i.e. there is a corresponding value for the record and it is greater than 0), set the LockID to -1.
Access doesn't like it as "Operation must use an updateable query"
How can I write this kind of UPDATE query where the update is conditional on the output of a separate LEFT-JOIN'd query?
Thanks!
This is the basic syntax of the query :
SQL:
PARAMETERS SpecifiedRecordID Long;
UPDATE tblRecords R
LEFT JOIN qryOutstanding AS Q ON R.RecordID = Q.RecordID
SET R.LockID = SWITCH(Nz(Q.Outstanding,0)>0,-1,TRUE,0)
WHERE R.RecordID = SpecifiedRecordID;
So basically, look for the ID in the query, if the corresponding value for the record in question is 0, or the record is not present at all, set the LockID to 0, otherwise (i.e. there is a corresponding value for the record and it is greater than 0), set the LockID to -1.
Access doesn't like it as "Operation must use an updateable query"
How can I write this kind of UPDATE query where the update is conditional on the output of a separate LEFT-JOIN'd query?
Thanks!