Dynamic Quarter Select Query Help 2

[Bennie47250]

Dynamic Quarter Select Query Help

Using Access 2003

Would like help in creating a query that can select the previous quarter whenever it is ran.

For example:

If ran in Jan, Feb or March 2006, it select records for the 4th quarter of 2005.
If ran in April, May or June 2006, it selects records for the 1st quarter of 2006
If ran in July, Aug or Sept. 2006, it selects records for the 2nd quarter of 2006
If ran in Oct, Nov Dec 2006 it selects records for the 3rd quarter of 2006
If ran in Jan, Feb or March 2007, it select records for the 4th quarter of 2006.

So on and so on……

Thanks
Bennie

 

[Golom]

This will compute the first day of the quarter that you want

DateSerial(Year(DateAdd("q", -1, Date())), 
          (((Month(DateAdd("q", -1, Date())) - 1) \ 3) * 3) + 1, 1)

For purposes of preserving your sanity however, I would suggest a function

Public Function SQ(Increment As Integer, Limit As String) As Date
    Dim QuarterDate                 As Date
    Dim mn                          As Integer
    QuarterDate = DateAdd("q", Increment, Date())
    mn = (((Month(DateAdd("q", Increment, Date())) - 1) \ 3) * 3) + 1
    SQ = DateSerial(Year(QuarterDate), mn, 1)
    If Ucase(Left(Limit,1)) = "E" then
        SQ = DateSerial(Year(SQ), Month(SQ) + 3, 0)
    End If
End Function

and then your SQL is just

Select ... From myTable

WHERE TheDate BETWEEN SQ(-1, "Start") AND SQ(-1, "End")

 

[Bennie47250]

Before I received my Tek-Tips e-mail notification, I developed the following formulas to do this:

For the year I used: IIf(DatePart("q",Date())=1,Year(Date())-1,Year(Date()))

For the quarter I used: IIf(DatePart("q",Date())=1,4,IIf(DatePart("q",Date())=2,1,IIf(DatePart("q",Date())=3,2,IIf(DatePart("q",Date())=3,4,0))))

While I’m sure the code from Golom is much cleaner, I could not figure out how it worked so I just used what I developed.

 

[Golom]

If "Date()" is in the fourth quarter then

IIf(DatePart("q",Date())=1,4,
IIf(DatePart("q",Date())=2,1,
IIf(DatePart("q",Date())=3,2,
IIf(DatePart("q",Date())=3,4,0))))

Returns zero. Perhaps you need

IIf(DatePart("q",Date())=1,4,
IIf(DatePart("q",Date())=2,1,
IIf(DatePart("q",Date())=3,2,
IIf(DatePart("q",Date())=[red]4,3[/red],0))))

 

[PHV]

For the quarter use:
IIf(DatePart('q',Date())=1,4,DatePart('q',Date())-1)

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