Returning Start and End Date Given Week Number

[unclefink]

I am trying to build a report that will be generated weekly showing data of cases over a specific week number (User will type in week number in a form).

What I am trying to do is create a query that I can place on the top of the report showing the physical start and ending day of the week the user entered.

For Example:

User Chooses Week 2.
Top of the report shows January 6, - January 12.

Anyone have any suggestions?

 

[Isskint]

The following function will return the Monday of the given Week number

Public Function WeekStart(intWhichWeek As Integer, intWhichYear As Integer) As Date
 
Dim intWeekDay As Integer
Dim dtNewYear, dtYearStart As Date
 
dtNewYear = DateSerial(intWhichYear, 1, 1)
intWeekDay = (dtNewYear - 2) Mod 7 'Generate weekday index where Monday = 0
 
If intWeekDay < 4 Then
    dtYearStart = dtNewYear - intWeekDay
Else
    dtYearStart = dtNewYear - intWeekDay + 7
End If
 
WeekStart = dtYearStart + ((intWhichWeek - 1) * 7)
 
End Function

Just add/subtract to get your date range

 

[ChrisO]

Caution when using Week Number…

There is a bug in Access which can return week 53 when it should be week 1.

Microsoft has supplied a workaround which implements the ISO 8601 standard:-

http://support.microsoft.com/kb/200299

Chris.