Your problem isn't with this query--its with one of the 2 queries its built on. Its looking for a PastRevenueDate field in the query called qryPastRevenue. Either qry704 or qry705 is built on qryPastRevenue and it can't find it. You need to run qry704 and qry705 to see which one gives this error--most likely qry705.
You're right--I found the issue and fixed that. Taking your code and changing field/table names to my actual query, here's what I ran:
qry706_subLastRevenueDates
SELECT qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch, qry704_AssumpSumGroupedByProdRevType.AssumpDate, qry705_PastRevenue.RevenueDate, qry705_PastRevenue.PastRevenue
FROM qry704_AssumpSumGroupedByProdRevType INNER JOIN qry705_PastRevenue ON (qry704_AssumpSumGroupedByProdRevType.AssumpDateMinus1 = qry705_PastRevenue.RevenueDate) AND (qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch = qry705_PastRevenue.ProdRevType)
WHERE (((qry704_AssumpSumGroupedByProdRevType.AssumpDate)>=[qry705_PastRevenue].[RevenueDate]) AND ((qry705_PastRevenue.PastRevenue)>0));
query2
SELECT qry704_AssumpSumGroupedByProdRevType.AssumpDate, qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch, [qry704_AssumpSumGroupedByProdRevType.SumOfCurrFcst_Amt]+Nz([qry706_subLastRevenueDates.PastRevenue]) AS ForecastAmount
FROM (qry704_AssumpSumGroupedByProdRevType LEFT JOIN qry706_subLastRevenueDates ON (qry704_AssumpSumGroupedByProdRevType.AssumpDate = qry706_subLastRevenueDates.AssumpDate) AND (qry704_AssumpSumGroupedByProdRevType.ProdRevTypeMatch = qry706_subLastRevenueDates.ProdRevTypeMatch)) LEFT JOIN qry705_PastRevenue ON (qry706_subLastRevenueDates.RevenueDate = qry705_PastRevenue.RevenueDate) AND (qry706_subLastRevenueDates.ProdRevTypeMatch = qry705_PastRevenue.ProdRevType);
but and again it seemed to add the July Revenue to the August assumptions as it should but didn't so it with the following months. see attached result.