# Thread: Week Number (Access 2k)

1. ## 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. ## 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. ## 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
•