Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Kenton, Delaware, USA
    Thanked 0 Times in 0 Posts

    First Weekday of the Week (2000)

    I am sure this question has been answered about a million times, but I am going to risk asking it anyway. I have a report that is grouped by week. How do I format the group text box to show a date and not just the number of the week? Ideally the date would read "<Monday's Date> Thru <Friday's Date>" but I can certainly live with just "The Week starting <Sunday's date>". The first date in the detail section is not always on a Monday.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Richland, Washington, USA
    Thanked 0 Times in 0 Posts

    Re: First Weekday of the Week (2000)


    Here's an approach I use to deal with this sort of situation. Create a query (if you haven't already) that produces the recordset for your report. As a field in that query, include an expression something like:

    <font face="Georgia">WeekOf: dteDate - Weekday(dteDate, 2) + 1</font face=georgia>

    where dteDate is your date. This calculates the Monday of the week for each date. (Note that in this example Saturday and Sunday "belong to" the previous Monday.) Similarly you can add another field to calculate the Sunday after your date:

    <font face="Georgia">WeekEnds: dteDate - Weekday(dteDate, 2) + 7</font face=georgia>

    You can adjust the second argument of the Weekday function to indicate which day you want your grouping to start on (1=Sunday, 2=Monday, etc.)

    You can then use one or both of these fields in the group heading in your report. For example, in your header (grouped on [WeekOf]), create an unbound text control and specify as its Control Source:

    <font face="Georgia">=[WeekOf] & " through " & [WeekEnds]</font face=georgia>

    Alternatively, you could leave the WeekEnds out of the query and just put the expression into the Control Source calculation:

    <font face="Georgia">=[WeekOf] & " through " & ([dteDate] - Weekday([dteDate], 2) + 7)</font face=georgia>

    There may be more elegant ways of doing this, but I think this gets you what you want.

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts