Week Number by Month

[islu0102]

I'm needing to write an expression that calculates the week number for any date for the month it's in, given that all weeks begin on Monday and all months begin on the first Monday of the month.

In other words, the 5th week of October, 2011 would begin on 10/31/11 and end on 11/06/11. The first week of November would then begin on 11/7/11, etc.

The resulting query would display something like the following:

10/27/11 OCTOBER 4
10/28/11 OCTOBER 4
10/31/11 OCTOBER 5
11/02/11 OCTOBER 5
11/08/11 NOVEMBER 1

I have been able to get the month to display correctly using the following expression:

IIf(Weekday([py end date],2)=1,Format([py end date],"mmmm"),Format([py end date]+1-Weekday([py end date],2),"mmmm"))

I can get the week number in Excel, but can't seem to figure out how to do the same in Access. Any help would be greatly appreciated!

 

[plog]

I don't think you can get it to a simple expression. I was able to calculate it in a VBA function. Here's what you need to do to use it.

In your query put a field like this:

MonthWeek: getWeekNum([YourDateFieldHere])

Be sure to put the name of the field in your database that contains the date you want to calculate on instead of 'YourDateFieldHere'.

Next, create a module and paste in this code:

Function getFirstMonday(d)
    ' returns day of month 1st Monday occurs in the month of the date it is passed
ret = 1
Select Case Weekday(Month(d) & "/1/" & Year(d))
   Case 1
        ret = 2
   Case 2
        ret = 1
   Case 3
        ret = 7
   Case 4
        ret = 6
   Case 5
        ret = 5
   Case 6
        ret = 4
   Case 7
        ret = 3
End Select
getFirstMonday = ret
End Function
Function getWeekNum(d)
    ' returns month name and week number that d falls on within that month
ret = 1
FirstMonday = getFirstMonday(d)
    ' holds day of month first monday of reporting date falls on
MonthBegin = Month(d)
    ' holds month first monday of reporting date falls on
YearBegin = Year(d)
    ' holds year first monday of reporting date falls on
If Day(d) < FirstMonday Then
    ' if d comes before 1st monday has occured in current month, rolls back reporting month to prior one
    MonthBegin = MonthBegin - 1
    If (MonthBegin < 1) Then
        MonthBegin = 12
        YearBegin = YearBegin - 1
        End If
    FirstMonday = getFirstMonday(MonthBegin & "/1/" & YearBegin)
    ' calculates first monday that occurs in month prior to d
    End If
    
    
ret = MonthName(MonthBegin) & " - " & Int((DateDiff("d", MonthBegin & "/" & FirstMonday & "/" & YearBegin, d) + 1) / 7) + 1
    ' calculates week number based on first monday of reporting day minus d
 
getWeekNum = ret
End Function

Run the query and it should return the name of a month and the week number that date calculates to.

 

[islu0102]

Fantastic!

This solution worked perfectly! Thank you, Plog, for the work you put into that code.