Week Ending

[ewong1]

I've done a search on week ending in the reports forum and am having some trouble finding a solution to my problem.

I have a report set up that groups by week, and it pulls the first date in the week grouping as the header for that week. What I am having trouble figuring out is how to make it return the last day of the week. For example:

Friday
8/4/2000 43,963 0 43,138
8/4/2000 27,622 0 27,080
8/5/2000 13,123 0 13,753
8/5/2000 77,874 0 75,606
8/5/2000 57,880 0 56,745
8/5/2000 5,530 0 5,242
8/5/2000 37,595 0 36,755

The data above is one grouping in my report. because 8/4 is the first record in that grouping, it is listing friday as the first day. I would like it to actually say Saturday because that is when my reporting period ends.

Hopefully I have made my request clear. let me know if you have questions. Thanks!

 

[VanRoekel.Clint]

End of week

If your reporting period is always Saturday, don't use a variable. Just put the label "Saturday" in the header.

 

[ewong1]

I guess the data that i provided was not representative of what I am attempting to accomplish. The grouping is actually supposed to read Saturday August 5, 2000, Saturday August 12, 2000, ...

 

[jalldridge]

Not sure if I follow exactly what you are after... Can you not use the format function?

I had a issue in that I needed to get the day at the end of the week when only having information for a day during that week

I used the following bit of code in my case... it might be useful to you..

Function endOfWeek(startDate As Date) As String

    Dim dayNumber As Integer
    Dim extraDays As Integer
    
    dayNumber = Weekday(startDate, vbSunday)
    extraDays = 7 - dayNumber

    endOfWeek = DateAdd("d", extraDays, startDate)
    
End Function