subquery delaying many months

[Kyle02]

Hey,

I have a sub query that gets the previous data. I want to re write it to get the previous data by 4 months. How would i go about doing that?

 (SELECT MAX(T2.MonthlyDate)      
FROM tblDates AS T2     
WHERE T2.ProductID=tblDates.[ProductID]  
AND T2.MonthlyDate < tblDates.MonthlyDate) As PriorDate

How can i set it up to change the last month, to 4 months ago.. IE>

MonthlyDate....PriorDate
31/01/2013
28/02/2013
31/03/2013
30/04/2013


31/05/2013.....31/01/2013
And so on....

Thanks.

[June7]

Do you want the criteria to be 4 months from the current date? Maybe:

AND T2.MonthlyDate < DateAdd("m",-4,Date()) As PriorDate

[Kyle02]

That picks a date. It then goes from the MAX Date in the Selection, and minus 4 months off of it.
right now it shows:
MonthlyDate....PriorDate
31/01/2013.....
28/02/2013.....31/01/2013
31/03/2013.....28/02/2013
30/04/2013.....31/03/2013
31/05/2013.....30/04/2013

I want it to show:
MonthlyDate....PriorDate
31/01/2013
28/02/2013
31/03/2013
30/04/2013
31/05/2013.....31/01/2013
30/06/2013.....28/02/2013

[June7]

You want to subtract 4 months from the Max?

The subquery can return only 1 value so maybe:

(SELECT MAX(DateAdd("m",-4,T2.MonthlyDate))
FROM tblDates AS T2
WHERE T2.ProductID=tblDates.[ProductID]
AND T2.MonthlyDate < tblDates.MonthlyDate) As PriorDate

or

(SELECT DateAdd("m",-4,Max(T2.MonthlyDate))
FROM tblDates AS T2
WHERE T2.ProductID=tblDates.[ProductID]
AND T2.MonthlyDate < tblDates.MonthlyDate) As PriorDate