What's wrong with my syntax here??

UPDATE PastForecast_Append1 LEFT JOIN PastForecast
ON (PastForecast_Append1.Noun=PastForecast.Noun) AND (PastForecast_Append1.FcstDate=PastForecast.Date)
SET PastForecast.Noun = pastforecast_Append1.noun, PastForecast.[Date] = pastforecast_append1.fcstdate, PastForecast.Fcst = pastforecast_append1.fcstqty, PastForecast.EntryDate = getdate();

This works fine in Access 2002 but when I try it in SQL server I keep getting "Incorrect syntax near the keyword 'LEFT'.

I'm only guessing here, It looks like you are using a keyword "Date" in your string.

Also I noticed that "date" is enclosed in square brackets in one place and not another.
AND (PastForecast_Append1.FcstDate=PastForecast.Date)
PastForecast.[Date] = pastforecast_append1.fcstdate,

I changed to
UPDATE PastForecast_Append1 LEFT JOIN PastForecast
ON (PastForecast_Append1.Noun=PastForecast.Noun) AND (PastForecast_Append1.FcstDate=PastForecast.dDate)
SET PastForecast.Noun = pastforecast_Append1.noun, PastForecast.dDate = pastforecast_append1.fcstdate, PastForecast.Fcst = pastforecast_append1.fcstqty, PastForecast.EntryDate = getdate();
but still get the same error

An update query in T-SQL is not formulated the same as in Access. You need to move things around a bit.

Look here and see if you can spot what needs to change:
http://msdn.microsoft.com/en-us/library/aa260662(SQL.80).aspx

As a stating point, you query needs to look something like this (as the link from Bob's post shows);


UPDATE PastForecast
SET
PastForecast.Noun = pastforecast_Append1.noun,
PastForecast.[Date] = pastforecast_append1.fcstdate,
PastForecast.Fcst = pastforecast_append1.fcstqty,
PastForecast.EntryDate =getdate()
FROM
PastForecast_Append1 LEFTJOIN PastForecast ON(PastForecast_Append1.Noun=PastForecast.Noun)
AND(PastForecast_Append1.FcstDate=PastForecast.Dat e)


One caveat if you are using datetime fields in SQL server, you may find that the two tables never find a matching record, as datetime is precise to seconds.

Thanks much, that did the trick.