Setting up Quarterly Report Tracking Logs

[03SCBeast]

I’m trying to set up a query that automatically tracks due dates for 30 day, QR 1, QR 2, QR 3, and QR 4 reports based on the anniversary date. The anniversary date is calculated from the mm/dd of the effective date and the current “yyyy”.

So far, I used the code below is as follows:

Code to calculate Anniversary Date and to update to current year:

IIf(DateDiff("yyyy",[Effective_Date],Date())>1,Format([Effective_Date],"mm/dd") & "/" & Format(Date(),"yyyy"),[Effective_Date])

Code to calculate due dates for reports:
[30 Day]: IIf(DateDiff("d",[Effective_Date],Date())>365, "N/A",DateAdd("d",30.4375,[Anniversary_Date]))
[QR 1]: DateAdd("d",91.3125,[Anniversary_Date])
[QR 2]: DateAdd("d",182.625,[ Anniversary_Date])
[QR 3]: DateAdd("d",273.9375,[ Anniversary_Date])
[QR 4]: DateAdd("d",365.25,[ Anniversary_Date])

Problem with this code: I get inaccurate results if I want to enter an effective date more than 1 year prior to Date(). I.e., if I entered 10/12/2002, my anniversary date becomes 10/12/05 and my first QR 1 isn’t due until 1/12/2006 when it should be 01/12/2005. Otherwise, formula works fine. How can I modify the formula that calculates the anniversary date to fix this problem? Perhaps I could write a formula to do away with anniversary date altogether and base reports on effective date.

Your suggestions would be greatly appreciated as I'm sure this wheel has already been invented (lol).

P.S.- I realize these functions will only give approximate due dates due to leap years but this is sufficient for the purposes I wish to use it for.

 

[PHV]

Anniversary Date: DateSerial(Year(Now)+(Format([Effective_Date],'mmdd')>Format(Now,'mmdd')),Month([Effective_Date]),Day([Effective_Date]))
30 Day: IIf(Date()-[Effective_Date]>365,'N/A',DateAdd('m',1,[Anniversary_Date]))
QR 1: DateAdd('q',1,[Anniversary_Date])
QR 2: DateAdd('q',2,[ Anniversary_Date])
QR 3: DateAdd('q',3,[ Anniversary_Date])
QR 4: DateAdd('q',4,[ Anniversary_Date])



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[03SCBeast]

Thanks again PHV...running out of time today but will try first thing tomorrow. The parts of your formulas I have tried so far get the job done well. Have a great evening.