DatePart

[cochise]

DatePart ("ww",Date) gives you the week number for a date. How can I get the dates for a given week number?

 

[BoxHead]

Cochise,

You have to be able to identify the year along with the week number, but that shouldn't be a problem. I hard-coded this for 2002 to keep it simple.

The following returns the date for Sunday of the week number specified.

Private Sub C20_Click()
Dim weekNum As Integer, DayOfWeek As Date, FirstOfWeek As Date
weekNum = T6
DayOfWeek = DateAdd("ww", weekNum, #1/1/2002#)
FirstOfWeek = DateAdd("d", -(6 + WeekDay(#1/1/2002#)), DayOfWeek)
T7 = FirstOfWeek
End Sub

All this does is add the number of weeks specified to the first day of the appropriate year and then back up to the Sunday of the preceding week.

Be aware that Access views Decmber dates in the last week of the year as the 53rd week and views January dates of that week as the first week of the following year.


HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[tlbroadbent]

You can use a function similar to the following. You may need to modify depending on how you define the first week of the year. This function assumes that the week that contains Jan. 1 is the first week of a year.

Public Function WeekNoToFirstDayOfWeek _
(iYear As Integer, iWkNo As Integer) As Date
iWkNo = iWkNo - 1
WeekNoToFirstDayOfWeek = _
DateAdd("ww", iWkNo, DateSerial(iYear, 1, 1)) - _
Weekday(DateAdd("ww", iWkNo, _
DateSerial(iYear, 1, 1))) + 1
End Function

Call the function as follows.

txtBox1 = _
Format(WeekNoToFirstDayOfWeek(2001,26), "mm/dd/yy&quot
txtBox2 = Dateadd("d", 6, txtBox1) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.