# Thread: Week of Month (Access 2000 (9.0.2720))

1. ## Week of Month (Access 2000 (9.0.2720))

I am looking for a function that would group dates by the week of the month. For example, today is 12/19/02 which would be the 3rd week of December. The datepart function only gives me the week of the year. Does anyone know how to do this?

Thanks,
Larry

2. ## Re: Week of Month (Access 2000 (9.0.2720))

The DatePart function will give you numbered weeks for the year. See Access help for the syntax.

Here is some code that I use to get numbered weeks. WorkWk: DatePart("ww",[MyDateFieldHere],"2")
The 2 in the expression is used to specify Monday as the first day of the Week. Sunday is 1.

I use the expression in a query.

3. ## Re: Week of Month (Access 2000 (9.0.2720))

I created some logic like that for a data warehouse once, but it made my head ache. The problem is in figuring out what to count as a week in a month. If the month starts on a Wednesday, do you count that in the new month or the prior month? What do you do with partial weeks? On the other hand, if you're looking for something like a form to allow users to select a report for "last week", "2 weeks ago", etc., you don't really need to number weeks in a month.

Perhaps if you explain exactly what you're tyring to accomplish, someone can help you.

4. ## Re: Week of Month (Access 2000 (9.0.2720))

The day function returns the day of the month, a number between 1 and 31.

The week number would usually be 1 more than the result of dividing this by 7 and ignoring the remainder. If there is no remainder, the don't add 1.

function fnweeknumber (mydate as date) as integer
Dim dayofMonth as integer
Dim weeknumber as integer
Dayofmonth = day(mydate)

if (dayofmonth mod 7 = 0) then
weeknumber = dayofmonth 7
else
weeknumber = 1 + dayofmonth 7
end if
fnweeknumber = weeknumber
end function
The mod function just gives the remainder
The function just returns the integer component of the division

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•