ms-accessjet

MS Access UPDATE with JOIN on three conditions

I'm trying to update records through a single inner join with multiple criteria. My best effort so far is this:

UPDATE FormData d 
INNER JOIN ProductGrowthDays g 
ON d.ProductCode = g.ProductCode AND 
ON d.ProductionLineCode = g.ProductionLineCode AND 
ON g.MonthIndex = MONTH(d.SowingDate)
SET d.EstimatedDays = g.GrowingDays
WHERE
d.EventTypeId = 1

Access gives the error 'Syntax error (missing operator)' and highlights the 'r' in 'd.ProductCode'. The join is guaranteed to give a single row.

Could anyone give me pointers on how to fix this?

Solution

  • D'oh. The answer was as follows:

    UPDATE FormData d 
INNER JOIN ProductGrowthDays g 
ON (d.ProductCode = g.ProductCode 
AND d.ProductionLineCode = g.ProductionLineCode 
AND g.MonthIndex = MONTH(d.SowingDate))
SET d.EstimatedDays = g.GrowingDays
WHERE
d.EventTypeId = 1

    I was sure I tried that at one point, but obviously not. Well, leaving this here if someone else should need it.