Background: I've designed a query that tracks due dates for quarterly reports based on the anniversary date. I've used PHV's code to ensure the anniversary date is relative to the time the person is entered into the system and it all works perfectly.
Problem: I need to modify query so that QR1DUE corresponds to any reports due between 01/01 and 03/31, QR2DUE (reports due between 04/01 and 06/30 and so forth) regardless of the date a person is entered into the system.
Example: Juan gets entered into the system on 6/7/05. His QR1Due should be 03/07/06 as opposed to 09/07/05, QR2Due s/b 6/7/06, QR3Due s/b 09/07/05, QR4Due s/b 12/07/05. Dale gets entered on 12/05/05, QR1,2,3,4 s/b 3/5/06, 6/5/06, 9/5/06, and 12/5/06 respectively.
Here's the partial code for the query based on fields:
[Effective_Date],[Anniversary_Date], [QR1Due], [QR2Due]...
[Anniversary_Date]: DateSerial(Year(Now())+(Format([Effective_Date],'mmdd')>Format(Now(),'mmdd')),Month([Effective_Date]),Day([Effective_Date]))
[REPORT1DUE]
ateAdd('q',1,[Anniversary_Date])
[REPORT2DUE]ateAdd('q',2,[Anniversary_Date])
Problem: I need to modify query so that QR1DUE corresponds to any reports due between 01/01 and 03/31, QR2DUE (reports due between 04/01 and 06/30 and so forth) regardless of the date a person is entered into the system.
Example: Juan gets entered into the system on 6/7/05. His QR1Due should be 03/07/06 as opposed to 09/07/05, QR2Due s/b 6/7/06, QR3Due s/b 09/07/05, QR4Due s/b 12/07/05. Dale gets entered on 12/05/05, QR1,2,3,4 s/b 3/5/06, 6/5/06, 9/5/06, and 12/5/06 respectively.
Here's the partial code for the query based on fields:
[Effective_Date],[Anniversary_Date], [QR1Due], [QR2Due]...
[Anniversary_Date]: DateSerial(Year(Now())+(Format([Effective_Date],'mmdd')>Format(Now(),'mmdd')),Month([Effective_Date]),Day([Effective_Date]))
[REPORT1DUE]
ateAdd('q',1,[Anniversary_Date])[REPORT2DUE]
ateAdd('q',2,[Anniversary_Date])
