SQL SubQuery Does Not Work

[saascuba]

I am having a problem with a subquery in an update statement.

The first example works:

UPDATE Equip_Info
INNER JOIN Clean_Data
ON Equip_Info.Equip_No = Clean_Data.Equip_No
SET Equip_Info.Next_Clean_Date =
FORMAT(DATEADD("d", 7,
(DMax("Date_Time", "Clean_Data", "Clean_Data.Equip_No='1'"))), 'mm-dd-yyyy'),
Edit_Date='11/4/2010 2:23:39 PM'
where Equip_Info.Equip_No ='1'

But when I add a sub-query to obtain the second parameter of the DATEADD I get an error when the SQL statement is run.

UPDATE Equip_Info
INNER JOIN Clean_Data
ON Equip_Info.Equip_No = Clean_Data.Equip_No
SET Equip_Info.Next_Clean_Date =
FORMAT(DATEADD("d", select Clean_Frequency from Equip_Info where Equip_No='1',
(DMax("Date_Time", "Clean_Data", "Clean_Data.Equip_No='1'"))), 'mm-dd-yyyy'),
Edit_Date='11/4/2010 2:15:34 PM'
where Equip_Info.Equip_No ='1'

The field being queried by the sub-query is numeric so I am at a loss as to what is happening.



Any ideas?

saascuba

[pbaldy]

You can't have SQL inside the DateAdd() like that. You could have a DLookup() there.

[saascuba]

Thanks for the steer, that worked

[pbaldy]

No problemo.