Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Burlington, Washington, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    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
    Regards
    John



Posting Permissions

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