Query for quarter end date, depending on current date.

[Gizmodo]

2) if current date is between 3/25 and 4/8
then show records where open date between (4/1 - 3/31) <--Within a 3 year period ofcourse

I realize that now there will be gaps where if the query is opened on an unspecified date nothing shall happen, as such, it would be appropriate to expand the time frame of when a report is generated so that every day of the year is covered.

quarter 1 if current date within (2/15 and 5/14)
quarter 2 if current date within (5/15 and 8/14)


quarter 3 if current date within (8/15 and 11/14)
quarter 4 if current date within (11/15 and 2/14)

I hope that makes sense.

[orange]

Ok, I have adjusted to use the intYearsback (if Month is 12, then go back 2 years, otherwise 3 years) and have 10 simulated current dates.


You have this
if current date is between 3/25 and 4/8 then show records where open date between (4/1 - 3/31) <--Within a 3 year period of course but what are the consequences if the current date happens to be 3/21?????

0  ************  Simulated current date M/D/Y  3/27/2012
Check for Month/Day  between  3/25 to 4/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              1
quarterEnd .  . .       . . . .. .. . .. .. .  ...   3/31/2012
ReportperiodStart .................................  4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     4/8/2012
-------   Adjusted Dates ------
quarter                       1
Adjusted quarterEnd           3/31/2012
Adjusted ReportperiodStart    4/1/2009
QtrLo                         3/25/2012
QtrHi                         4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2009# AND #3/31/2012#;

1  ************  Simulated current date M/D/Y  3/21/2012
Check for Month/Day  between  3/25 to 4/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              1
quarterEnd .  . .       . . . .. .. . .. .. .  ...   3/31/2012
ReportperiodStart .................................  4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     4/8/2012
-------   Adjusted Dates ------
quarter                       1
Adjusted quarterEnd           3/31/2012
Adjusted ReportperiodStart    4/1/2009
QtrLo                         3/25/2012
QtrHi                         4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2009# AND #3/31/2012#;

2  ************  Simulated current date M/D/Y  4/3/2013
Check for Month/Day  between  6/25 to 7/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              2
quarterEnd .  . .       . . . .. .. . .. .. .  ...   6/30/2012
ReportperiodStart .................................  7/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    6/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     7/8/2012
-------   Adjusted Dates ------
quarter                       2
Adjusted quarterEnd           6/30/2013
Adjusted ReportperiodStart    7/1/2010
QtrLo                         6/25/2012
QtrHi                         7/8/2012
Search  sql will be Select records from cases where Opendate Between #7/1/2010# AND #6/30/2013#;

3  ************  Simulated current date M/D/Y  12/29/2012
Check for Month/Day  between  12/25 to 1/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              4
quarterEnd .  . .       . . . .. .. . .. .. .  ...   12/31/2012
ReportperiodStart .................................  1/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    12/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     1/8/2013
-------   Adjusted Dates ------
quarter                       4
Adjusted quarterEnd           12/31/2012
Adjusted ReportperiodStart    1/1/2010
QtrLo                         12/25/2012
QtrHi                         1/8/2013
Search  sql will be Select records from cases where Opendate Between #1/1/2010# AND #12/31/2012#;

4  ************  Simulated current date M/D/Y  7/15/2010
Check for Month/Day  between  9/25 to 10/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              3
quarterEnd .  . .       . . . .. .. . .. .. .  ...   9/30/2012
ReportperiodStart .................................  8/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    9/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     10/8/2012
-------   Adjusted Dates ------
quarter                       3
Adjusted quarterEnd           9/30/2010
Adjusted ReportperiodStart    8/1/2007
QtrLo                         9/25/2012
QtrHi                         10/8/2012
Search  sql will be Select records from cases where Opendate Between #8/1/2007# AND #9/30/2010#;

5  ************  Simulated current date M/D/Y  3/27/2011
Check for Month/Day  between  3/25 to 4/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              1
quarterEnd .  . .       . . . .. .. . .. .. .  ...   3/31/2012
ReportperiodStart .................................  4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     4/8/2012
-------   Adjusted Dates ------
quarter                       1
Adjusted quarterEnd           3/31/2011
Adjusted ReportperiodStart    4/1/2008
QtrLo                         3/25/2012
QtrHi                         4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2008# AND #3/31/2011#;

6  ************  Simulated current date M/D/Y  3/21/2010
Check for Month/Day  between  3/25 to 4/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              1
quarterEnd .  . .       . . . .. .. . .. .. .  ...   3/31/2012
ReportperiodStart .................................  4/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    3/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     4/8/2012
-------   Adjusted Dates ------
quarter                       1
Adjusted quarterEnd           3/31/2010
Adjusted ReportperiodStart    4/1/2007
QtrLo                         3/25/2012
QtrHi                         4/8/2012
Search  sql will be Select records from cases where Opendate Between #4/1/2007# AND #3/31/2010#;

7  ************  Simulated current date M/D/Y  6/26/2011
Check for Month/Day  between  6/25 to 7/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              2
quarterEnd .  . .       . . . .. .. . .. .. .  ...   6/30/2012
ReportperiodStart .................................  7/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    6/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     7/8/2012
-------   Adjusted Dates ------
quarter                       2
Adjusted quarterEnd           6/30/2011
Adjusted ReportperiodStart    7/1/2008
QtrLo                         6/25/2012
QtrHi                         7/8/2012
Search  sql will be Select records from cases where Opendate Between #7/1/2008# AND #6/30/2011#;

8  ************  Simulated current date M/D/Y  12/29/2011
Check for Month/Day  between  12/25 to 1/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              4
quarterEnd .  . .       . . . .. .. . .. .. .  ...   12/31/2012
ReportperiodStart .................................  1/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    12/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     1/8/2013
-------   Adjusted Dates ------
quarter                       4
Adjusted quarterEnd           12/31/2011
Adjusted ReportperiodStart    1/1/2009
QtrLo                         12/25/2012
QtrHi                         1/8/2013
Search  sql will be Select records from cases where Opendate Between #1/1/2009# AND #12/31/2011#;

9  ************  Simulated current date M/D/Y  12/8/1960
Check for Month/Day  between  12/25 to 1/8  OR ELSE WHAT ? ?? ?? ?? ?? ????*????*?*??
quarter                                              4
quarterEnd .  . .       . . . .. .. . .. .. .  ...   12/31/2012
ReportperiodStart .................................  1/1/2012
QtrLo  M/D before QtrEnd to include in Qtr Report    12/25/2012
QtrHi  M/D after QtrEnd to include in Qtr Report     1/8/2013
-------   Adjusted Dates ------
quarter                       4
Adjusted quarterEnd           12/31/1960
Adjusted ReportperiodStart    1/1/1958
QtrLo                         12/25/2012
QtrHi                         1/8/2013
Search  sql will be Select records from cases where Opendate Between #1/1/1958# AND #12/31/1960#;

[Gizmodo]

2) if current date is between 3/25 and 4/8
then show records where open date between (4/1 - 3/31) <--Within a 3 year period ofcourse

I realize that now there will be gaps where if the query is opened on an unspecified date nothing shall happen, as such, it would be appropriate to expand the time frame of when a report is generated so that every day of the year is covered.

quarter 1 if current date within (2/15 and 5/14)
quarter 2 if current date within (5/15 and 8/14)
quarter 3 if current date within (8/15 and 11/14)
quarter 4 if current date within (11/15 and 2/14)

I hope that makes sense.

[orange]

Based on this latest information, I have adjusted my table parameters. Here are the results of processing the 10 simulated current dates with the revised selection dates for each of the quarters. I hope this is the result you are looking for.
The table configuration (layout and data) and the procedure showing the logic follow these simulated outputs.

0 ************ Simulated current date M/D/Y 3/27/2012
Check for Month/Day between 2/15 to 5/14
quarter 1
quarterEnd . . . . . . .. .. . .. .. . ... 3/31/2012
ReportperiodStart ................................. 4/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 2/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 5/14/2012
------- Adjusted Dates ------
quarter 1
Adjusted quarterEnd 3/31/2012
Adjusted ReportperiodStart 4/1/2009
QtrLo 2/15/2012
QtrHi 5/14/2012
Search sql will be Select records from cases where Opendate Between #4/1/2009# AND #3/31/2012#;

1 ************ Simulated current date M/D/Y 3/21/2012
Check for Month/Day between 2/15 to 5/14
quarter 1
quarterEnd . . . . . . .. .. . .. .. . ... 3/31/2012
ReportperiodStart ................................. 4/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 2/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 5/14/2012
------- Adjusted Dates ------
quarter 1
Adjusted quarterEnd 3/31/2012
Adjusted ReportperiodStart 4/1/2009
QtrLo 2/15/2012
QtrHi 5/14/2012
Search sql will be Select records from cases where Opendate Between #4/1/2009# AND #3/31/2012#;

2 ************ Simulated current date M/D/Y 4/3/2013
Check for Month/Day between 5/15 to 8/14
quarter 2
quarterEnd . . . . . . .. .. . .. .. . ... 6/30/2012
ReportperiodStart ................................. 7/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 5/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 8/14/2012
------- Adjusted Dates ------
quarter 2
Adjusted quarterEnd 6/30/2013
Adjusted ReportperiodStart 7/1/2010
QtrLo 5/15/2012
QtrHi 8/14/2012
Search sql will be Select records from cases where Opendate Between #7/1/2010# AND #6/30/2013#;

3 ************ Simulated current date M/D/Y 12/29/2012
Check for Month/Day between 11/15 to 2/14
quarter 4
quarterEnd . . . . . . .. .. . .. .. . ... 12/31/2012
ReportperiodStart ................................. 1/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 11/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 2/14/2013
------- Adjusted Dates ------
quarter 4
Adjusted quarterEnd 12/31/2012
Adjusted ReportperiodStart 1/1/2010
QtrLo 11/15/2012
QtrHi 2/14/2013
Search sql will be Select records from cases where Opendate Between #1/1/2010# AND #12/31/2012#;

4 ************ Simulated current date M/D/Y 7/15/2010
Check for Month/Day between 8/15 to 11/14
quarter 3
quarterEnd . . . . . . .. .. . .. .. . ... 9/30/2012
ReportperiodStart ................................. 10/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 8/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 11/14/2012
------- Adjusted Dates ------
quarter 3
Adjusted quarterEnd 9/30/2010
Adjusted ReportperiodStart 10/1/2007
QtrLo 8/15/2012
QtrHi 11/14/2012
Search sql will be Select records from cases where Opendate Between #10/1/2007# AND #9/30/2010#;

5 ************ Simulated current date M/D/Y 3/27/2011
Check for Month/Day between 2/15 to 5/14
quarter 1
quarterEnd . . . . . . .. .. . .. .. . ... 3/31/2012
ReportperiodStart ................................. 4/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 2/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 5/14/2012
------- Adjusted Dates ------
quarter 1
Adjusted quarterEnd 3/31/2011
Adjusted ReportperiodStart 4/1/2008
QtrLo 2/15/2012
QtrHi 5/14/2012
Search sql will be Select records from cases where Opendate Between #4/1/2008# AND #3/31/2011#;

6 ************ Simulated current date M/D/Y 3/21/2010
Check for Month/Day between 2/15 to 5/14
quarter 1
quarterEnd . . . . . . .. .. . .. .. . ... 3/31/2012
ReportperiodStart ................................. 4/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 2/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 5/14/2012
------- Adjusted Dates ------
quarter 1
Adjusted quarterEnd 3/31/2010
Adjusted ReportperiodStart 4/1/2007
QtrLo 2/15/2012
QtrHi 5/14/2012
Search sql will be Select records from cases where Opendate Between #4/1/2007# AND #3/31/2010#;

7 ************ Simulated current date M/D/Y 6/26/2011
Check for Month/Day between 5/15 to 8/14
quarter 2
quarterEnd . . . . . . .. .. . .. .. . ... 6/30/2012
ReportperiodStart ................................. 7/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 5/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 8/14/2012
------- Adjusted Dates ------
quarter 2
Adjusted quarterEnd 6/30/2011
Adjusted ReportperiodStart 7/1/2008
QtrLo 5/15/2012
QtrHi 8/14/2012
Search sql will be Select records from cases where Opendate Between #7/1/2008# AND #6/30/2011#;

8 ************ Simulated current date M/D/Y 12/29/2011
Check for Month/Day between 11/15 to 2/14
quarter 4
quarterEnd . . . . . . .. .. . .. .. . ... 12/31/2012
ReportperiodStart ................................. 1/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 11/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 2/14/2013
------- Adjusted Dates ------
quarter 4
Adjusted quarterEnd 12/31/2011
Adjusted ReportperiodStart 1/1/2009
QtrLo 11/15/2012
QtrHi 2/14/2013
Search sql will be Select records from cases where Opendate Between #1/1/2009# AND #12/31/2011#;

9 ************ Simulated current date M/D/Y 12/8/1960
Check for Month/Day between 11/15 to 2/14
quarter 4
quarterEnd . . . . . . .. .. . .. .. . ... 12/31/2012
ReportperiodStart ................................. 1/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 11/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 2/14/2013
------- Adjusted Dates ------
quarter 4
Adjusted quarterEnd 12/31/1960
Adjusted ReportperiodStart 1/1/1958
QtrLo 11/15/2012
QtrHi 2/14/2013
Search sql will be Select records from cases where Opendate Between #1/1/1958# AND #12/31/1960#;

The Table layout, the data values and the procedure code are as follows:

'---------------------------------------------------------------------------------------
' Procedure : Gizmodo_Qtr
' Author    : Jack
' Date      : 09-11-2012
' Purpose   : Used to help with the logic for selecting Cases For Quarter reporting
'see: this link for the rationale and email trail
'https://www.accessforums.net/queries/query-quarter-end-date-depending-current-date-29796/index2.html
'
' This procedure deals with data in this table layout( tblQtrsWithRanges)
'
'table_name        field_name        field_description          ordinal_position    data_type   length
'tblQtrsWithRanges   id              Identifier PK                           0      Long         4
'tblQtrsWithRanges   qtrStartDate    First day of quarter                    1      Date         8
'tblQtrsWithRanges   ReportStartDate Date on which Reports will be selected  2      Date         8
'tblQtrsWithRanges   qtrEndDate      Last day of Quarter                     3      Date         8
'tblQtrsWithRanges   qtrLow          Min M/D  to be selected for Quarter Rpt 4      Date         8
'tblQtrsWithRanges   qtrHi           Max M/D  to be selected for Quarter Rpt 5      Date         8
'
'The Table Data Values are as follows:
'
'id  qtrStartDate    ReportStartDate qtrEndDate  qtrLow     qtrHi
'
'1   1/1/2012    4/1/2012           3/31/2012   2/15/2012   5/14/2012
'2   4/1/2012    7/1/2012           6/30/2012   5/15/2012   8/14/2012
'3   7/1/2012    10/1/2012          9/30/2012   8/15/2012   11/14/2012
'4   10/1/2012   1/1/2012           12/31/2012  11/15/2012  2/14/2013
'
' The Year portion of these Dates should be updated to  Year(Date) each year
' and should work for the logic as used in this procedure.
'
'---------------------------------------------------------------------------------------
' Last Modified:
'
'LATEST INFO
'I realize that now there will be gaps where if the query is opened on an
'unspecified date nothing shall happen, as such, it would be appropriate to expand the time frame of when a report is generated so that every day of the year is covered.
'
'quarter 1 if current date within (2/15 and 5/14)
'quarter 2 if current date within (5/15 and 8/14)
'quarter 3 if current date within (8/15 and 11/14)
'quarter 4 if current date within (11/15 and 2/14)
'
'
'
'ORIGINAL INFO
'
'If current date is between 12/26/(Current Year) and
'1/7/(Next year in this case since the year will be 1+)-
'search for records where date opened between
'(1/1/(Current Year -2 if current month = 12 OR
'Current Year -3 if current month i= 1)
' - 12/31/(Current Year if month = 12 OR previous year if current month =1)
'
'If current date is between 3/26/(Current Year) and
'4/7/(Current Year)- search for records where date opened
'between (4/1/(current year - 3) - 3/31(Current Year))
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub Gizmodo_Qtr()
   
    Dim CurDate(10) As Date    'Simulate Current dates
    Dim i As Integer           'loop control
    Dim sql As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Dim intYearsBack As Integer    'how far back in years .. this is the 3 normally,but 2 if Q4
    Dim Quarter As Integer         'which Quarter
    Dim QuarterStart As Date       'the Quarter Start date
    Dim ReportPeriodStart As Date  'The ReportPeriod Start Date
    Dim QuarterEnd As Date         'the Quarter End Date
    Dim Qtrlow As Date             'The Lowest Date for this Quarter Report
    Dim Qtrhi As Date              'The Highest Date for this Quarter Report
    Dim ActiveCurDate As Date      'The Active Cuurent Date
    Dim CurMonth As Integer   'current Month
    Dim CurDay As Integer     'current day
    Dim CurYear As Integer    'current year
    Dim activeQtr As Integer  'derived from the ActiveCurDate

    On Error GoTo Gizmodo_Qtr_Error

'Popuate Simulated Current Dates Array
    CurDate(0) = #3/27/2012#
    CurDate(1) = #3/21/2012#
    CurDate(2) = #4/3/2013#
    CurDate(3) = #12/29/2012#
    CurDate(4) = #7/15/2010#
    CurDate(5) = #3/27/2011#
    CurDate(6) = #3/21/2010#
    CurDate(7) = #6/26/2011#
    CurDate(8) = #12/29/2011#
    CurDate(9) = #12/8/1960#
    
    ' Loop through the Simulated Current Dates to develop the logic
    ' for selecting Cases Records for the appropriate Quarter Reports
    
    For i = 0 To 9
        ActiveCurDate = CurDate(i)  'the simulated current date for this iteration
        Debug.Print i & "  ********  Simulated -ActiveCurDate- current date M/D/Y  " & ActiveCurDate
        'simulate selecting cases
        CurDay = Day(ActiveCurDate)
        CurMonth = Month(ActiveCurDate)
        CurYear = Year(ActiveCurDate)

        Select Case CurMonth  'determining the Quarter to be active
        Case 1, 2, 3
            activeQtr = 1
        Case 4, 5, 6
            activeQtr = 2
        Case 7, 8, 9
            activeQtr = 3
        Case 10, 11, 12
            activeQtr = 4
        Case Else
            MsgBox "Invalid Month in ActiveCurDate"
        End Select

        sql = "Select id, qtrStartDate,qtrEndDate,ReportStartDate ,qtrLow, qtrHi from tblQtrsWithRanges where Id = " & activeQtr
        Set rs = db.OpenRecordset(sql)

        'Get Base dates from Quarters table
        Quarter = rs!Id
        QuarterStart = rs!qtrStartDate
        ReportPeriodStart = rs!ReportStartDate
        QuarterEnd = rs!qtrEndDate
        Qtrlow = rs!Qtrlow
        Qtrhi = rs!Qtrhi
        Debug.Print "Check for Month/Day  between  " & Month(Qtrlow) & "/" & Day(Qtrlow) & " to " & Month(Qtrhi) & "/" & Day(Qtrhi)
        '  Show Base Dates
        Debug.Print "quarter                                              " & Quarter & vbCrLf _
                    & "quarterEnd .  . .       . . . .. .. . .. .. .  ...   " & QuarterEnd & vbCrLf _
                    & "ReportperiodStart .................................  " & ReportPeriodStart & vbCrLf _
                    & "QtrLo  M/D before QtrEnd to include in Qtr Report    " & Qtrlow & vbCrLf _
                    & "QtrHi  M/D after QtrEnd to include in Qtr Report     " & Qtrhi

        'ADJUST dates based on Current Date info
        ' if Month = 12 then only 2 years back
        ' otherwise 3 years back

        'How far back for opened records

        If CurMonth = 12 Then
            intYearsBack = 2
        Else
            intYearsBack = 3
        End If
        '******************************
        QuarterStart = DateSerial(CurYear - intYearsBack, Month(QuarterStart), Day(QuarterStart))
        ReportPeriodStart = DateSerial(CurYear - intYearsBack, Month(ReportPeriodStart), Day(ReportPeriodStart))
        QuarterEnd = DateSerial(CurYear, Month(QuarterEnd), Day(QuarterEnd))
        '
        '(Debug.prints for Debugging purposes----------------)
        'Show adjusted Dates
        Debug.Print "-------   Adjusted Dates ------" & vbCrLf _
                    & "quarter                       " & Quarter & vbCrLf _
                    & "Adjusted quarterEnd           " & QuarterEnd & vbCrLf _
                    & "Adjusted ReportperiodStart    " & ReportPeriodStart & vbCrLf _
                    & "QtrLo                         " & Qtrlow & vbCrLf _
                    & "QtrHi                         " & Qtrhi
                    
        'Show the SQL for Selecting Cases  for the Quarter Report
        Debug.Print "Search  sql will be " _
                    & "Select records from cases where Opendate Between #" _
                    & ReportPeriodStart & "# AND #" & QuarterEnd & "#;" & vbCrLf

    Next i  'get the next Simulated Current Date
    On Error GoTo 0
    Exit Sub

Gizmodo_Qtr_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Gizmodo_Qtr of Module AWF_Related"

End Sub

[Gizmodo]

I found two simulations that provide the incorrect quarter from the current date.

2 ************ Simulated current date M/D/Y 4/3/2013
Check for Month/Day between 5/15 to 8/14
quarter 2
quarterEnd . . . . . . .. .. . .. .. . ... 6/30/2012
ReportperiodStart ................................. 7/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 5/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 8/14/2012
------- Adjusted Dates ------
quarter 2
Adjusted quarterEnd 6/30/2013
Adjusted ReportperiodStart 7/1/2010
QtrLo 5/15/2012
QtrHi 8/14/2012
Search sql will be Select records from cases where Opendate Between #7/1/2010# AND #6/30/2013#;


4 ************ Simulated current date M/D/Y 7/15/2010
Check for Month/Day between 8/15 to 11/14
quarter 3
quarterEnd . . . . . . .. .. . .. .. . ... 9/30/2012
ReportperiodStart ................................. 10/1/2012
QtrLo M/D before QtrEnd to include in Qtr Report 8/15/2012
QtrHi M/D after QtrEnd to include in Qtr Report 11/14/2012
------- Adjusted Dates ------
quarter 3
Adjusted quarterEnd 9/30/2010
Adjusted ReportperiodStart 10/1/2007
QtrLo 8/15/2012
QtrHi 11/14/2012


Otherwise, they are exactly what I need!!! Thank you for your assistance.

Im assuming I start the process by creating another table in access that outlines the quarter information as you outlined above? Forgive me, I just dont know how i would implement this logic into code for access.

[orange]

Thanks for the feedback. I was using the current date to get the month and from that get the Quarter. However, with the latest date ranges for selecting reports that doesn't work.
What I have done in this set up is to determine the Day number in the year for the Lowest Date for the quarter and the Highest Date for the quarter.

for example:

id	qtrStartDate	ReportStartDate	qtrEndDate	qtrLow	qtrHi	LowDay	HiDay
1	1/1/2012	4/1/2012	3/31/2012	2/15/2012	5/14/2012	45	134
2	4/1/2012	7/1/2012	6/30/2012	5/15/2012	8/14/2012	135	226
3	7/1/2012	10/1/2012	9/30/2012	8/15/2012	11/14/2012	227	318
4	10/1/2012	1/1/2012	12/31/2012	11/15/2012	2/14/2013	319	410

I have also included Jan1 to Feb14 in quarter 4 (in code).
So this version checks the Day of Year number of the Simulated current date to determine which Quarter is involved in the reporting.

Here are the results for 15 simulated dates.

0  ********  Simulated -ActiveCurDate- current date M/D/Y  3/27/2012
Active Curdate  3/27/2012  is Day number 86
Check for Month/Day  between  2/15 to 5/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #4/1/2009# AND #3/31/2012#;
----  Going back  3  years


1  ********  Simulated -ActiveCurDate- current date M/D/Y  3/21/2012
Active Curdate  3/21/2012  is Day number 80
Check for Month/Day  between  2/15 to 5/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #4/1/2009# AND #3/31/2012#;
----  Going back  3  years


2  ********  Simulated -ActiveCurDate- current date M/D/Y  4/3/2013
Active Curdate  4/3/2013  is Day number 93
Check for Month/Day  between  2/15 to 5/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #4/1/2010# AND #3/31/2013#;
----  Going back  3  years


3  ********  Simulated -ActiveCurDate- current date M/D/Y  12/29/2012
Active Curdate  12/29/2012  is Day number 363
Check for Month/Day  between  11/15 to 2/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #1/1/2010# AND #12/31/2012#;
----  Going back  2  years


4  ********  Simulated -ActiveCurDate- current date M/D/Y  7/15/2010
Active Curdate  7/15/2010  is Day number 196
Check for Month/Day  between  5/15 to 8/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #7/1/2007# AND #6/30/2010#;
----  Going back  3  years


5  ********  Simulated -ActiveCurDate- current date M/D/Y  4/27/2011
Active Curdate  4/27/2011  is Day number 117
Check for Month/Day  between  2/15 to 5/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #4/1/2008# AND #3/31/2011#;
----  Going back  3  years


6  ********  Simulated -ActiveCurDate- current date M/D/Y  3/21/2010
Active Curdate  3/21/2010  is Day number 80
Check for Month/Day  between  2/15 to 5/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #4/1/2007# AND #3/31/2010#;
----  Going back  3  years


7  ********  Simulated -ActiveCurDate- current date M/D/Y  6/26/2011
Active Curdate  6/26/2011  is Day number 177
Check for Month/Day  between  5/15 to 8/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #7/1/2008# AND #6/30/2011#;
----  Going back  3  years


8  ********  Simulated -ActiveCurDate- current date M/D/Y  12/29/2011
Active Curdate  12/29/2011  is Day number 363
Check for Month/Day  between  11/15 to 2/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #1/1/2009# AND #12/31/2011#;
----  Going back  2  years


9  ********  Simulated -ActiveCurDate- current date M/D/Y  12/8/1990
Active Curdate  12/8/1990  is Day number 342
Check for Month/Day  between  11/15 to 2/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #1/1/1988# AND #12/31/1990#;
----  Going back  2  years


10  ********  Simulated -ActiveCurDate- current date M/D/Y  8/14/1993
Active Curdate  8/14/1993  is Day number 226
Check for Month/Day  between  5/15 to 8/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #7/1/1990# AND #6/30/1993#;
----  Going back  3  years


11  ********  Simulated -ActiveCurDate- current date M/D/Y  8/15/2001
Active Curdate  8/15/2001  is Day number 227
Check for Month/Day  between  8/15 to 11/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #10/1/1998# AND #9/30/2001#;
----  Going back  3  years


12  ********  Simulated -ActiveCurDate- current date M/D/Y  2/8/2009
Active Curdate  2/8/2009  is Day number 38
Check for Month/Day  between  11/15 to 2/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #1/1/2007# AND #12/31/2009#;
----  Going back  2  years


13  ********  Simulated -ActiveCurDate- current date M/D/Y  11/8/1960
Active Curdate  11/8/1960  is Day number 312
Check for Month/Day  between  8/15 to 11/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #10/1/1957# AND #9/30/1960#;
----  Going back  3  years


14  ********  Simulated -ActiveCurDate- current date M/D/Y  3/9/1999
Active Curdate  3/9/1999  is Day number 68
Check for Month/Day  between  2/15 to 5/14
Search  sql for your  Reports would be: 
    SELECT records FROM cases WHERE Opendate Between #4/1/1996# AND #3/31/1999#;
----  Going back  3  years

Code to determine quarter from current (simulated) date is

  Select Case WhatDayOfYearIsCurDate
        Case 45 To 134       'equates to 2/15  to 5/14  
            activeQtr = 1
        Case 135 To 226      'equates to 5/16  to 8/14
            activeQtr = 2
        Case 227 To 318      'equates to 8/15  to 11/14
            activeQtr = 3
        Case 319 To 410      'equates to 11/15  to 2/14
            activeQtr = 4
        Case 1 To 44         'need to deal with Jan 1 to Feb 13
            activeQtr = 4
        Case Else
            MsgBox "The current date being used is outside accepted range"
        End Select

Check these with your understanding of the reporting requirements. If you want to proceed, I can help you with the code.

Good luck with your project.

[Gizmodo]

Thanks so much for your help Orange.

So ive attempte to implement this logic into actuall access cod, but cant seem to grasp how it is actually done, I definately understand the logic, but am not that advanced in sql as to prepare proper code.

I created another table (quarters) that carries the columns ID, qtrStartDate, ReportStartDate, qtrEndDate, qtrLow, qtrHigh, LowDay, HiDay

Is that the only other table i would need to create? Im under the assumption that this table will still need to be updated annualy to reflect the current year.

Would the case statement go into the query after select statement, or would it still follow the where statement?

[orange]

I created the table to hold the relevant info for qtrstart/end, and the dates and day numbers to be used for determining the Report Start date.
Can you post any code relevant to the selection of the dates? And I will try to help with where certain things go.

You should review a little SQL at w3schools.com just for familiarity.
An SQL Select query can have an optional WHERE Clause. These are not two separate statements in "SQL" talk.

It depends on how you actually process your information as to where things go. The set up I developed was based on working out the proper qtr based on the current date (all based on your examples and explanation).

The goal was to get the proper report (and reporting dates) based on the current date. I showed an example of the Select statement to be used, but you could use the values obtained to be parameters in a query.

The table should be updated annually. You could just update the Year of each Date field since the qtrs won't changes and the day number is fixed.

[Gizmodo]

So i decided to create 4 queries pertaining to each quarter as it is a bit simpler for me to code

WHERE ([County Master List].[Date Opened]) between #3/31/(datepart("yyyy",now())-3)# and #3/31/(datepart("yyyy",now()))#

I am getting a syntax error, any ideas?

[Gizmodo]

come to think of it, the code should actually be

WHERE ([County Master List].[Date Opened]) between #4/1/(datepart("yyyy",now()-3))# and #3/31/(datepart("yyyy",now()))#

[orange]

I think you should be using DateSerial(). but I'm not sure your dates match the criteria you gave me earlier.
Also the way you have it

between #4/1/(datepart("yyyy",now())-3)# and #3/31/(datepart("yyyy",now()))#

it seems you are looking for Dates between 1Apr some year and 31March same year.

If you aren't going to do some vba or sql review, then you should be checking the functions and syntax at
http://www.techonthenet.com

Good luck with your project.