Week

  • Thread starter Thread starter Luis
  • Start date Start date
L

Luis

Hello.
I'd appreciate some help on the following issue.
I have a week number, and i need to now, using that week
number what was the date of the first day of that week.
For me the week starts on Monday.

Is this possible to do? I can use VBA or Expression
Builder because this is to include on a Query.


Thanks in advance.

Luis
 
Look at either the DatePart or Format function in the Help file (DatePart
will return a numeric value, Format will return the number as a string).
Both of them accept optional arguments firstdayofweek and firstweekof year
which allow you to specify the first day of the week and the first week of
the year respectively.
 
My fault: I misread your question.

You should be able to use DateAdd to add the week number to January 1st.
Then, determine the previous Monday to whatever date you derive.

Determining the previous Monday shouldn't be that difficult, but I don't
have sample code handy. Basically, you use the Weekday function to determine
what day of the week it currently is (use the vbMonday parameter, so that
Monday becomes day 1, Tuesday day 2 and so on), and and subtract the right
number of days to go back to Monday.
 
Luis,

Which would be:
Public Function GetMondayOfWeek(iWeekNo As Integer) As Date
Dim tmpDate As Date

tmpDate = DateAdd("ww", iWeekNo, DateSerial(Year(Date), 1, 1))
GetMondayOfWeek = tmpDate - (Weekday(tmpDate, vbMonday) - 1)
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top