# Thread: Find Which Week (Access 2003)

1. ## Find Which Week (Access 2003)

Is there a function in Access that can be used to determine the week of the month.
For example, January 23 is in the 4th week of January. February 1 is in the 5th week of January.

I have created a function to find this, but the function is not very dynamic. The date will
need to be updated each year.

Thanks for you help.

2. ## Re: Find Which Week (Access 2003)

Do you mean the "numeric" week (1st week always begins on the 1st day of the month, 2nd week begins on the 8th, 3rd week on the 15th, etc., or do you want to look at calendar weeks (Sunday through Saturday, or perhaps Monday through Sunday)?

added: sorry, I see that you don't want the "numeric" week. But please explain the definition of "week" you want to use - what determines that February 1 is in the 5th week of January?

3. ## Re: Find Which Week (Access 2003)

Calendar week. Looking at the calendar, the first week of January is January 1 - 6, second week is 7 - 13.
The fifth week is 1/28 - 2/3. Week one for February would be 2/4 - 2/10.

4. ## Re: Find Which Week (Access 2003)

Why doesn't week 1 start on December 31?

5. ## Re: Find Which Week (Access 2003)

Technically I guess it would, but they want all the reporting to begin on 1/1/2007.
Any function I use can use 12/31/2006 as the beginning of week one.
My dataset would take care of that by the fact that it does not include data for that day.

6. ## Re: Find Which Week (Access 2003)

Try this function:

Function WeekInMonth(dtmDate As Date) As Integer
Dim dtmSun As Date
dtmSun = dtmDate - Weekday(dtmDate) + 1
If Year(dtmSun) < Year(dtmDate) Then
' Special case - beginning of January
WeekInMonth = 1
Else
WeekInMonth = (Day(dtmSun) - 1) 7 + 1 - (Month(dtmSun) = 1)
End If
End Function

It can be used in VBA code, and also in expressions in queries and in the control source of a text box on a form or report. Example of use:

=WeekInMonth([StartDate])

#### Posting Permissions

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