[AccessD] Need to derive the "Start" and "End" dates from a "DatePart Week Number"

Hi Brad and David

First, there is no need to fall back to string handling of dates to solve this. Also, you cannot from the week number alone determine the date, because week numbers cross year boundaries meaning that the first week may contain dates from the previous year and the last week may contain dates from the following year. Thus, you need the year of the week as well:

<code>
Public Function ISO_DateOfWeek( _
  ByVal intYear As Integer, _
  ByVal bytWeek As Byte, _
  Optional ByVal bytWeekday As Byte = vbMonday) _
  As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

  ' The fourth of January is always included in
  ' the first week of year intYear.
  Const cbytDayOfFirstWeek  As Byte = 4
  ' Number of days in a week.
  Const cbytDaysOfWeek      As Byte = 7
  ' Month of January.
  Const cbytJanuary         As Byte = 1
  
  Dim datDateOfFirstWeek    As Date
  Dim intISOMonday          As Integer
  Dim intISOWeekday         As Integer
  Dim intWeekdayOffset      As Integer
  
  ' No specific error handling.
  On Error Resume Next
    
  If intYear > 0 Then
    ' Weekday of Monday.
    intISOMonday = Weekday(vbMonday, vbMonday)
    ' Date of fourth of January in year intYear.
    datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
    ' Weekday of fourth of January in year intYear.
    intISOWeekday = Weekday(datDateOfFirstWeek, vbMonday)
    ' Calculate offset from Monday in first week of year intYear.
    intWeekdayOffset = intISOMonday - intISOWeekday
    
    ' Weekday of requested weekday.
    intISOWeekday = Weekday(bytWeekday, vbMonday)
    ' Calculate offset from requested weekday in first week of year intYear.
    intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
    ' Date of requested weekday in first week of year intYear.
    datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)
  
    ' Date of requested weekday in requested week of year intYear.
    datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
  End If
  
  ISO_DateOfWeek = datDateOfFirstWeek
  
End Function
</code>

Run this with vbMonday to find the first day of the week in question.

However, in your case you do have a date initially, and from this you can directly calculate the first and last date of the week from that date:

<code>
Public Function DateWeekFirst( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = vbUseSystemDayOfWeek) _
  As Date

' Returns the first date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.
' 2003-05-01. System settings used as default.
    
  ' No special error handling.
  On Error Resume Next
  
  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbUseSystemDayOfWeek
  End Select
  
  DateWeekFirst = DateAdd("d", vbSunday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
    
End Function

Public Function DateWeekLast( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = vbUseSystemDayOfWeek) _
  As Date

' Returns the last date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.
' 2003-05-01. System settings used as default.
    
  ' No special error handling.
  On Error Resume Next
  
  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbUseSystemDayOfWeek
  End Select
      
  DateWeekLast = DateAdd("d", vbSaturday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
    
End Function
</code>

>>> BradM at blackforestltd.com 09-08-12 22:43 >>>
All,

I have a small report that summarizes the number of new orders by week.
The report's underlying query uses "DatePart" like this.


OrderWeek: DatePart("ww",[OrderDate])



Here is a small sample of what the report looks like.

~~~~~~~~~~~~
Week  Nbr-of-Orders
16         333
17         355 
~~~~~~~~~~~~~  

I have been asked to spell out what the start and end dates are for each
week number on the report.

Is it possible to derive the "Start" and "End" dates from a week number?

Thanks,
Brad