help with SQL

[pduncan]

I am trying to copy some data from one table to another. When I run the SQL, I get this error message:

"Invavid Meme, OLE, or Hyperlink Object in subquery 'tblControlActivityMaster.Description'"

I have double checked my spelling - and I am at a loss. Access help comes up blank (Literally).

Here is my SQL

UPDATE tblAuditPlan
SET tblAuditPlan.TestDescription = (SELECT tblControlActivityMaster.Description FROM tblControlActivityMaster WHERE tblControlActivityMaster.ActivityID = tblAuditPlan.ActivityID)



PDUNCAN - MEMPHIS, TN

“Friends help you hide. Real friends help you hide bodies.”

 

[cghoga]

try this...

put extra parens around what's included after the WHERE.

UPDATE tblAuditPlan
SET tblAuditPlan.TestDescription = (SELECT tblControlActivityMaster.Description FROM tblControlActivityMaster WHERE (tblControlActivityMaster.ActivityID = tblAuditPlan.ActivityID))

if this doen't work, try this...

Include both tables in the FROM section of the subquery.

UPDATE tblAuditPlan
SET tblAuditPlan.TestDescription = (SELECT tblControlActivityMaster.Description FROM tblControlActivityMaster, tblAuditPlan WHERE (tblControlActivityMaster.ActivityID = tblAuditPlan.ActivityID))

did this help any?

 

[pduncan]

Hi - thanks for the help.
I tried both ways - I still get the same error message:
"Invalid Memo"
I went in and verified that null values are OK -

One thing also..
if tblControlActivityMaster.Description has null fields, would the UPDATE query overwrite tblAuditPlan.testDescription with a null (Assuming that tblAuditPlan.testDescription has text in it previously)?
If so - how could one avoid that?

I also tried a join to make a new table, then rename it -
it didnt work either

PDUNCAN - MEMPHIS, TN

“Friends help you hide. Real friends help you hide bodies.”