Report Group intervals by period

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....
 
J

Jerry Whittle

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.
 
J

John W. Vinson

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....

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.
 
J

John Spencer

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....

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.
 
J

John W. Vinson

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

Right you are, John - thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top