We help IT Professionals succeed at work.

How to Handle "Week Ending" date in Access

I just began using Microsoft Access and am creating a database to handle Weekly Activity reporting. For example, during the week of 3/16/15 through 3/20/15, I sent 29 emails, received 12 emails, attended 6 meetings, etc.

 I am trying to figure out a way to handle the "Week Ending" date. For each weekly activity report, there must be a "Week Ending" date - which must be the Friday of the current week. Any suggestions on how to go about doing so?

 I have a Users table that holds the username, network id, and password. I also have a Weekly table that holds each element I want shown on my Weekly Activity report (e.g. emails sent, emails received, meetings attended, etc.).
Watch Question

Most Valuable Expert 2015
Distinguished Expert 2018
You can use this generic function:
Public Function DateNextWeekday( _
  ByVal datDate As Date, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Returns the date of the next weekday, as spelled in vbXxxxday, following datDate.
' 2000-09-06. Cactus Data ApS.
  ' No special error handling.
  On Error Resume Next
  DateNextWeekday = DateAdd("d", 7 - (Weekday(datDate, bytWeekday) - 1), datDate)
End Function

Open in new window

To get the week ending as Friday for each date in your record, use something like this.
= [NameOfDateField] + (7 - Weekday([NameOfDateField], vbSaturday))
Or this:
= [NameOfDateField] + (7 - Weekday([NameOfDateField], 7)

DT               WeekEnding
3/18/15      3/20/15
3/19/15      3/20/15
3/20/15      3/20/15
3/21/15      3/27/15
3/22/15      3/27/15