Report Group intervals by period

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

SoggyCashew

Hello, Im using a report wizard and im "grouping levels" by date and I wanted
to "grouping intervals" by period (Every 4 months- 3 periods in a year)
anyway there isnt and option for that just quarter, normal, year month ect.
How can I do this in my report? Thanks....
 
Create the report using Month. After the wizard creates the report open it in
design view. In the report's sorting and grouping options set the Group On
property to Month and the Group Interval to 4.
 
Hello, Im using a report wizard and im "grouping levels" by date and I wanted
to "grouping intervals" by period (Every 4 months- 3 periods in a year)
anyway there isnt and option for that just quarter, normal, year month ect.
How can I do this in my report? Thanks....
Click to expand...

An additional suggestion, as an alternative to Jerry's, would be to use a
calculated field in the query:

PeriodNo: DateDiff("m", #1/1/2000#, [datefield]) \ 4

using the integer divide operator \ will give values 1 for 1/1/2000-4/30/2000;
2 for 5/1/2000-9/30/2000 and so on. You can use this for your grouping and
sorting. You can of course use whatever date you want as the beginning of the
first period.
 
John and Chad,

Pardon me but I think you need to use
PeriodNo: (DateDiff("m", #1/1/2000#, [datefield])-1) \ 4

That will return
0 for months 1 to 4
1 for months 5 to 8
2 for months 9 to 12

If you want 1, 2, and 3 then add 1 to the result
PeriodNo: 1 + (DateDiff("m", #1/1/2000#, [datefield])-1) \ 4

John's original would return
0 for months 1 to 3
1 for months 4 to 7
2 for months 8 to 11
3 for month 12

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

Hello, Im using a report wizard and im "grouping levels" by date and I wanted
to "grouping intervals" by period (Every 4 months- 3 periods in a year)
anyway there isnt and option for that just quarter, normal, year month ect.
How can I do this in my report? Thanks....
Click to expand...

An additional suggestion, as an alternative to Jerry's, would be to use a
calculated field in the query:

PeriodNo: DateDiff("m", #1/1/2000#, [datefield]) \ 4

using the integer divide operator \ will give values 1 for 1/1/2000-4/30/2000;
2 for 5/1/2000-9/30/2000 and so on. You can use this for your grouping and
sorting. You can of course use whatever date you want as the beginning of the
first period.
Click to expand...
 
John and Chad,

Pardon me but I think you need to use
PeriodNo: (DateDiff("m", #1/1/2000#, [datefield])-1) \ 4

That will return
0 for months 1 to 4
1 for months 5 to 8
2 for months 9 to 12

If you want 1, 2, and 3 then add 1 to the result
PeriodNo: 1 + (DateDiff("m", #1/1/2000#, [datefield])-1) \ 4

John's original would return
0 for months 1 to 3
1 for months 4 to 7
2 for months 8 to 11
3 for month 12
Click to expand...

Right you are, John - thanks!
 
Back
Top