Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Week Number (Access 2k)

    I have a field with a number in it that represents a year and a week(yyyyww). I need to convert this to a yyyy/mm/dd format. I need the Monday date of the week referenced. Can anyone help me?

    Thanks,
    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Number (Access 2k)

    There are several systems to calculate week numbers, so the following may have to be tweaked to suit your needs. The function below needs to be copied into a standard module:

    Public Function DateFromWeek(varWeek)
    Dim lngYear As Long
    Dim lngWeek As Long
    Dim dtmDate As Date
    If IsNull(varWeek) Then
    DateFromWeek = Null
    Else
    lngYear = varWeek 100
    lngWeek = varWeek Mod 100
    dtmDate = DateSerial(lngYear, 1, 1) + 7 * (lngWeek - 1)
    DateFromWeek = dtmDate - Weekday(dtmDate, vbTuesday) Mod 7
    End If
    End Function

    You can use it in queries, forms and reports. Say that your yyyyww field is named TheWeek. You can create a calculated column in a query:

    TheDate: DateFromWeek([TheWeek])

    or a text box on a form or report with control source

    =DateFromWeek([TheWeek])

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Week Number (Access 2k)

    Thanks,
    Tom

Posting Permissions

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