Query Problem

[PraizesLiberato]

Hi Everyone,

I looking for a way to get this query to work.

UPDATE tblCust_ExcessesHistory AS TBEH LEFT JOIN tbl_Excess AS TBEX ON TBEH.Excessid = TBEX.Excessid

SET

TBEH.RCUTargetDate = (SELECT TBEH2.RCUTargetDate FROM tblCust_ExcessesHistory AS TBEH2, tbl_Excess AS TBEX2 WHERE (((TBEH2.Excessid)=[TBEX2].[Excessid] And ((([TBEX2].[accountid])=[TBEX].[accountid]) And (([TBEX2].[DateOfExcess])=[TBEX].[dateofexcess]) And (([TBEH2].[ReportDate])=#22/03/2010#)))))

, TBEH.RCUComments = (SELECT TBEH2.RCUComments FROM tblCust_ExcessesHistory AS TBEH2, tbl_Excess AS TBEX2 WHERE (((TBEH2.Excessid)=[TBEX2].[Excessid] And ((([TBEX2].[accountid])=[TBEX].[accountid]) And (([TBEX2].[DateOfExcess])=[TBEX].[dateofexcess]) And (([TBEH2].[ReportDate])=#22/03/2010#)))))

WHERE (((TBEH.ReportDate)=#3/23/2010#));

Im trying to use this query to update the Comments from a previous days report to the latest day.

its sould be working but i keep getting the same error when i run it from the design mode.

ERROR:
Invalid Memo, OLE, or Hyperlink Object in subquery TBEH2.RCUComments

please help!!!

also i want to find a way to make my database even faster. i you have any ideas, please let me know.

 

[PKJ]

I am not sure but it seems that TBEH2 & TBEX2 variables in

TBEH.RCUComments = (SELECT TBEH2.RCUComments ... statement may be creating some conflict with earlier usage of these vars in

TBEH.RCUTargetDate = (SELECT ...

It is just a guess, I have not used such type of UPDATE .. SET statement in Access queries - Access does not support SQL standard completely.