Grouping by date - How To: Show the first day of the week instead of the first date entered in that

[Neil]

Hello All,

Access 2003:

I created a report for a table (by using the Wizard) and made a grouping
level on my date field. I then set the group on property to be Week. All is
well, the data is sorted by week. I just have one small requirement. The
wizard added the code below to show the week number in the Date by Week text
box:

=Format$([dteDate],"ww",0,0)

My report shows week 41 - which is correct. I also want it to show the date
of the Monday of each week so the overall text displayed would be something
like:

"Week commencing: 04th October - Week 41"

I have tried using:

="Week Commencing: " & Format$([dteDate],"dd-mmmm") & " - Week " &
Format$([dteDate],"ww",0,0)

This shows the date in the dates field - in my case the 7th October. I am
stumped as to how i can get the the first monday of the week. Can someone
please advise how this can be done?

TIA,

Neil.

 

[Marshall Barton]

Access 2003:

I created a report for a table (by using the Wizard) and made a grouping
level on my date field. I then set the group on property to be Week. All is
well, the data is sorted by week. I just have one small requirement. The
wizard added the code below to show the week number in the Date by Week text
box:

=Format$([dteDate],"ww",0,0)

My report shows week 41 - which is correct. I also want it to show the date
of the Monday of each week so the overall text displayed would be something
like:

"Week commencing: 04th October - Week 41"

I have tried using:

="Week Commencing: " & Format$([dteDate],"dd-mmmm") & " - Week " &
Format$([dteDate],"ww",0,0)

This shows the date in the dates field - in my case the 7th October. I am
stumped as to how i can get the the first monday of the week.

="Week Commencing: " & Format(dteDate - DatePart("w",
dteDate, 2) + 1, "dd-mmmm") & " - Week " &
Format$([dteDate], "ww",0,0)

 

[Neil]

Thanks Marsh,

That works great!

Neil.

Access 2003:

I created a report for a table (by using the Wizard) and made a grouping
level on my date field. I then set the group on property to be Week. All
is
well, the data is sorted by week. I just have one small requirement. The
wizard added the code below to show the week number in the Date by Week
text
box:

=Format$([dteDate],"ww",0,0)

My report shows week 41 - which is correct. I also want it to show the
date
of the Monday of each week so the overall text displayed would be
something
like:

"Week commencing: 04th October - Week 41"

I have tried using:

="Week Commencing: " & Format$([dteDate],"dd-mmmm") & " - Week " &
Format$([dteDate],"ww",0,0)

This shows the date in the dates field - in my case the 7th October. I am
stumped as to how i can get the the first monday of the week.

="Week Commencing: " & Format(dteDate - DatePart("w",
dteDate, 2) + 1, "dd-mmmm") & " - Week " &
Format$([dteDate], "ww",0,0)