Grouping by Dates in a Report

[Miguelito Bain]

hi-

i have a simple table. i have 3 fields, id, indate, and outdate. i
want to group these fields in my report by month. for example, for
february 2004, i want to display all the ids and dates for those
records with indates during the month of february. i'm trying to get
my report to look kind of like this...

MONTH CASEID INDATE OUTDATE

January 2004 1 1/10/04 1/24/04
2 1/15/04 2/10/04

TOTAL = 2

February 2002

1 2/1/04 2/15/04
45 2/5/04 2/22/04
8 2/22/04 2/24/04

TOTAL = 3
is there a way or function in the report wizard to group by months?

thanks,

miguel

[Larry Linson]

Use the Month function, the Year function, or Format, or all of them, to
extract the Month in the Query which you use as RecordSource for the Report.
Use the Report's Sorting and Grouping to group on Year and Month, and to
sort on Year and Month Number.

Larry Linson
Microsoft Access MVP

"Miguelito Bain" <mi***********@hotmail.com> wrote in message
news:1b**************************@posting.google.c om...

hi-

i have a simple table. i have 3 fields, id, indate, and outdate. i
want to group these fields in my report by month. for example, for
february 2004, i want to display all the ids and dates for those
records with indates during the month of february. i'm trying to get
my report to look kind of like this...

MONTH CASEID INDATE OUTDATE

January 2004 1 1/10/04 1/24/04
2 1/15/04 2/10/04

TOTAL = 2

February 2002

1 2/1/04 2/15/04
45 2/5/04 2/22/04
8 2/22/04 2/24/04

TOTAL = 3
is there a way or function in the report wizard to group by months?

thanks,

miguel

[Mike Storr]

On 23 Feb 2004 19:43:16 -0800, Miguelito Bain wrote:

hi-

i have a simple table. i have 3 fields, id, indate, and outdate. i
want to group these fields in my report by month. for example, for
february 2004, i want to display all the ids and dates for those
records with indates during the month of february. i'm trying to get
my report to look kind of like this...

MONTH CASEID INDATE OUTDATE

January 2004 1 1/10/04 1/24/04
2 1/15/04 2/10/04

TOTAL = 2

February 2002

1 2/1/04 2/15/04
45 2/5/04 2/22/04
8 2/22/04 2/24/04

TOTAL = 3
is there a way or function in the report wizard to group by months?

thanks,

miguel

The second step in the process allows you to create up to four grouping
levels. Once a level is defined, you can click on "Grouping Options" to
further define them.
--
Mike Storr
www.veraccess.com

[Allen Browne]

You want to list the case under the month heading if any part of that case
falls within the month.

The case should show under the month if:
- the case starts before the month ends, AND
- the case ends after the month starts.

To achieve this, you need a table of months (tblMonth) this this field:
MonthStart Date/Time

Create a query into tblMonth and your existing table.
No join between the 2 tables.

In the Criteria row under INDATE:
< DateAdd("m", 1, [MonthStart])
In the Criteria row under OUTDATE:

= [MonthStart]
If you wish to filter the query to particular months, filter on
tblMonth.MonthStart.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miguelito Bain" <mi***********@hotmail.com> wrote in message
news:1b**************************@posting.google.c om... hi-

i have a simple table. i have 3 fields, id, indate, and outdate. i
want to group these fields in my report by month. for example, for
february 2004, i want to display all the ids and dates for those
records with indates during the month of february. i'm trying to get
my report to look kind of like this...

MONTH CASEID INDATE OUTDATE

January 2004 1 1/10/04 1/24/04
2 1/15/04 2/10/04

TOTAL = 2

February 2002

1 2/1/04 2/15/04
45 2/5/04 2/22/04
8 2/22/04 2/24/04

TOTAL = 3
is there a way or function in the report wizard to group by months?

thanks,

miguel