Current year Quarterly totals

  • Thread starter Thread starter SoggyCashew
  • Start date Start date
S

SoggyCashew

Hello, I have a query that has a year field and I need formulas to find all 4
quarters of the field: Year: Year([inputdate]) and its criteria is
[Forms]![frmCalendar].[CalYear].

I found this formula for the current quarter but how would I use it to find
information for each quarter for the year selected?

Between DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
 
Your question is confusing, but what I think you want is the DatePart
Function or the Format Function.

Field: QuarterNumber: DatePart("q",[Some Date Field])

OR

Field: Quarter: Format([Some Date Field],"yyyy-q")

IF you are trying to get just one quarter at a time, you can pass an
additional parameter (quarter number) and apply that against the
calculated field.

Or pass the year and the quarter and use the following expression in the
where clause to return on specific quarter.

Between
DateSerial(Forms!frmCalendar!CalYear,(Forms!frmCalendar!Quarter-1)*3,1)
and DateSerial(Forms!frmCalendar!CalYear,(Forms!frmCalendar!Quarter*3)+1,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John im trying to get the 1st, 2nd , 3rd, and 4th quarter from the field
"Some date field"
--
Thanks,
Chad


John Spencer said:
Your question is confusing, but what I think you want is the DatePart
Function or the Format Function.

Field: QuarterNumber: DatePart("q",[Some Date Field])

OR

Field: Quarter: Format([Some Date Field],"yyyy-q")

IF you are trying to get just one quarter at a time, you can pass an
additional parameter (quarter number) and apply that against the
calculated field.

Or pass the year and the quarter and use the following expression in the
where clause to return on specific quarter.

Between
DateSerial(Forms!frmCalendar!CalYear,(Forms!frmCalendar!Quarter-1)*3,1)
and DateSerial(Forms!frmCalendar!CalYear,(Forms!frmCalendar!Quarter*3)+1,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello, I have a query that has a year field and I need formulas to find all 4
quarters of the field: Year: Year([inputdate]) and its criteria is
[Forms]![frmCalendar].[CalYear].

I found this formula for the current quarter but how would I use it to find
information for each quarter for the year selected?

Between DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+1,1) And
DateSerial(Year(Date()),((DatePart("q",Date())-1)*3)+4,0)
Click to expand...
Click to expand...
 
Back
Top