Date range by week on Report

  • Thread starter Thread starter Sunrise
  • Start date Start date
S

Sunrise

I would like to group data on a report by week but can't figure out how to
build the expression. Data comes from a date field in a query.

Group titles would be like "For the week of July 6 to July 13, 2009", for
example.

(Using Access 2003.)
 
Sunrise said:
I would like to group data on a report by week but can't figure out how to
build the expression. Data comes from a date field in a query.

Group titles would be like "For the week of July 6 to July 13, 2009", for
example.

(Using Access 2003.)
Click to expand...


Group on an expression like:

=DateAdd("d", 1 - WeekDay(datefield,2), datefield)
(the 2 means that Monday is the first day of the week)

You can use the same expression in the group header text
box's expression:

="For the week of " & Format(DateAdd("d", 1 -
WeekDay(datefield,2), datefield), "mmmm d") & " to " &
Format(DateAdd("d", 8 - WeekDay(datefield,2), datefield),
"mmmm d, yyyy")
 
When you select a date field in the sorting and grouping dialog, you have the
ability to set the Group On property to "Week".
 
Duane said:
When you select a date field in the sorting and grouping dialog, you have the
ability to set the Group On property to "Week".
Click to expand...


Duane, if you use the Week option, how do you specify the
week starting day?
 
"how do you specify the week starting day?"
I suppose if you didn't like the grouping default for week, you could
subtract a day or so in the field/expression.
 
Duane said:
"how do you specify the week starting day?"
I suppose if you didn't like the grouping default for week, you could
subtract a day or so in the field/expression.
Click to expand...


Ah, I did not think of that.
 
Back
Top