Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Richard

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Which Week (Access 2003)

    Why doesn't week 1 start on December 31?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Richard

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •