Date range by week on Report

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

 

[Marshall Barton]

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

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")

 

[Duane Hookom]

When you select a date field in the sorting and grouping dialog, you have the
ability to set the Group On property to "Week".

 

[Marshall Barton]

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, if you use the Week option, how do you specify the
week starting day?

 

[Duane Hookom]

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

 

[Marshall Barton]

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

Ah, I did not think of that.