How to determine Next Quarter if I know current quarter?

[clawlan]

I have a simple query that looks at today's date, references our corporate calendar, and displays the current fiscal quarter (eg. FY11-Q4). I am having trouble coming up with a way to determine the next quarter though.

The reason i am using these queries is because I then need to pass each of their results into a query that uses the quarter as a criteria.

So overall, user click form button "Run Current Quarter" and it would use the above query to determine the current quarter, then pass to the query. Then use can click "Run Next Quarter" and it would run a query and then pass this result to the query. Make sense?

 

[revans]

I use functions like these to return quarter info. You'll have to tweak them to return info for whatever your quarters are.

Public Function GetStartOfQuarter(i As Long, y As Long) As Date
'return start of quarter i(1,2,3, or 4) for year (y)
  Dim temp As Long
  Select Case i
    Case 1: temp = CDate("1/1/" & y)
    Case 2: temp = CDate("4/1/" & y)
    Case 3: temp = CDate("7/1/" & y)
    Case 4: temp = CDate("10/1/" & y)
  End Select
  GetStartOfQuarter = temp
End Function
 
Public Function GetStartOfQuarter2(d As Date) As Date
'return start of quarter d exists within (1,2,3,4)
  Dim temp As Date
  temp = GetStartOfQuarter(DatePart("q", d), Year(d))
  GetStartOfQuarter2 = temp
End Function
 
Public Function GetEndOfQuarter(i As Long, y As Long) As Date
'return end of quarter i(1,2,3, or 4) for year (y)
  Dim temp As Date
  Select Case i
    Case 1: temp = CDate("3/31/" & y)
    Case 2: temp = CDate("6/30/" & y)
    Case 3: temp = CDate("9/30/" & y)
    Case 4: temp = CDate("12/31/" & y)
  End Select
  GetEndOfQuarter = temp
End Function
 
Public Function GetEndOfQuarter2(d As Date) As Date
'return end of quarter d exists within (1,2,3,4)
  Dim temp As Date
  temp = GetEndOfQuarter(DatePart("q", d), Year(d))
  GetEndOfQuarter2 = temp
End Function

Once you know what your quarter is you should be able to just add one, or something

hth,

Rich