MS Access is really weird and requires brackets
Something like the following should work

UPDATE Productivity
SET Earned = R.Earned
, PercentComplete = R.PercentComplete
FROM ((Productivity INNER JOIN Productivity R ON R.WeekEndingDate =
#CreateODBCDate(dateadd("d",-7,todaysDate))#)
AND Productivity.WeekEndingDate = #CreateODBCDate(TodaysDate)#)

Or

UPDATE Productivity
SET Earned = R.Earned
, PercentComplete = R.PercentComplete
FROM Productivity INNER JOIN Productivity R ON R.WeekEndingDate =
#CreateODBCDate(dateadd("d",-7,todaysDate))#
WHERE Productivity.WeekEndingDate = #CreateODBCDate(TodaysDate)#