I just mocked up something to show the logic.
I deals with simulated current dates and some corresponding dates.
The QtrLow and QtrHi are the date ranges used to collect Opened Case records for reporting. Is this right?
Code:
Simulated current date 3/27/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 1
quarter 1
quarterEnd 3/31/2012
QtrLo 3/26/2012
QtrHi 4/7/2012
Search sql will be Select records from cases where Opendate Between #3/26/2009# AND #4/7/2012#;
Simulated current date 3/21/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 1
quarter 1
quarterEnd 3/31/2012
QtrLo 3/26/2012
QtrHi 4/7/2012
Search sql will be Select records from cases where Opendate Between #3/26/2009# AND #4/7/2012#;
Simulated current date 4/3/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 2
quarter 2
quarterEnd 6/30/2012
QtrLo 6/26/2012
QtrHi 7/7/2012
Search sql will be Select records from cases where Opendate Between #6/26/2009# AND #7/7/2012#;
Simulated current date 12/29/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 4
quarter 4
quarterEnd 12/31/2012
QtrLo 12/26/2012
QtrHi 1/7/2013
Search sql will be Select records from cases where Opendate Between #12/26/2010# AND #1/7/2013#;
Simulated current date 7/8/2012
Select id, qtrDate, qtrLow, qtrHi from tblQtrsWithRanges where Id = 3
quarter 3
quarterEnd 9/30/2012
QtrLo 9/26/2012
QtrHi 10/7/2012
Search sql will be Select records from cases where Opendate Between #9/26/2009# AND #10/7/2012#;