SOLVED

Update Query

Super Contributor

Hello experts, I have an update query SQL below and I am getting an error message.  Do you see anything obviously wrong with it? I am not sure why I am getting that error msg in the screen shot. 

 

UPDATE tblFXParent INNER JOIN qryFXSettDtCLast ON tblFXParent.IDFXParent = qryFXSettDtCLast.IDParentfk SET tblFXParent.DateSettleC = [qryFXSettDtCLast].[LastOfDateSettle];

Tony2021_2-1664654409202.png

 

thank you

 

 

 

 

6 Replies
I guess qryFXSettDtCLast is not updateable (looks like an aggregate qeury). That will make your update query not updateable.

@XPS35 

 

Ok that makes sense. qryFXSettDtCLast is an aggregate query. Do you happen to know of a workaround? I definitely need to update tblFXParent.DateSettleC to the LAST of Date Settle as found in [qryFXSettDtCLast]

If it helps, below is my sql of qryFXSettDtCLast
SELECT tblFXRollsChild.IDParentfk, Last(tblFXRollsChild.DateSettle) AS LastOfDateSettle
FROM tblFXRollsChild
GROUP BY tblFXRollsChild.IDParentfk;

@Tony2021 I am working on a tablet (Android} now and need to try some things first. I will be back later. 

best response confirmed by Tony2021 (Super Contributor)
Solution
UPDATE tblFXParent SET DateSettleC = DLOOKUP("LastOfDateSettle", "qryFXSettDtCLast", "IDParentfk = " & [IDFXParent])
Perfect! thank you very much!
you're welcome.