Control showing week commencing date

Question

Hi

I have a report which is grouped on a date field by week so all the dates within the week are grouped together.

Is it possible for the title of the group to contain a control that can work out the week commencing date?

So the header for each group on the report will say Week Commencing Monday [Monday's Date].

Thanks

Chris

Answer 1

Hi

Thanks for the replies.

I ended up using this in a text box:-

=[DateField]+1-Weekday([DateField]+6)

Chris

Answer 2

I use the following function:

Public Function WeekStart(intStartDay As Integer, Optional varDate As Variant)

    ' Returns 'week starting' date for any date

    ' Arguments:

    ' 1. intStartDay - weekday on which week starts, 1-7 (Sun - Sat)

    ' 2. vardate - optional date value for which week starting

    '   date to be returned.  Defaults to current date

    If IsMissing(varDate) Then varDate = VBA.Date

    If Not IsNull(varDate) Then

        WeekStart = DateValue(varDate) - Weekday(varDate, intStartDay) + 1

    End If

End Function

Answer 3

You should be able to use a text box with a control source like:

= DateAdd("d",-Weekday([YourDateFieldHere]), [YourDateFieldHere])+2