group by number of week of the month

[jhrs]

hi, is there a function in access to group by the number of week in a month, I have found one but it is for the whole year (53 weeks) and if i could count from the first day of that month, example if the month begins on friday it would be the first week til next thursday

Thanks a lot

 

[willir]

You could make your own function using DateDiff.

Function WeekInMonth(dtMyDate As Date) As Integer

Dim intFirstDay As Integer, dtFirstDay As Date

If IsDate(dtMyDate) Then
    dtFirstDay = Month(dtMyDate) & "/01/" & Year(dtMyDate)
    intFirstDay = Weekday(dtFirstDay)
    WeekInMonth = DateDiff("w", dtFirstDay, dtMyDate, intFirstDay) + 1
Else
    WeekInMonth = 0
End If

End Function

Then, with your SELECT statement...
SELECT WeekInMonth(YourDateField) From YourTable
GROUP BY WeekInMonth(YourDateField)

Richard