Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping data in week (Monday thru Sunday) (Access 2000)

    I am charting data grouped by week. The default setting defining week is Sunday through Saturday. I need to group the week as Monday through Sunday. The current format for the grouping is (Year([Date])*CLng(54)+DatePart("ww",[Date],0)-1). Is there something in this I can change to accomplish what I want?

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

    Re: Grouping data in week (Monday thru Sunday) (Access 2000)

    Have you tried using [Date] itself for the grouping level in the Sorting and Grouping window, and setting the Group On property to Week? If that doesn't work out correctly, you can experiment with =[Date]+1 or =[Date]-1 as grouping expression.

    Note: Date is a dangerous name for a field, since it is also a VBA function and a variable type. It is best to avoid using built-in names.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping data in week (Monday thru Sunday) (Access 2000)

    If you use this in code use :
    (Year([Date])*CLng(54)+DatePart("ww",[Date],vbMonday)-1)
    If in a query use :
    (Year([Date])*CLng(54)+DatePart("ww",[Date],2)-1).
    Francois

  4. #4
    Lounger
    Join Date
    Sep 2004
    Location
    Louisville, Kentucky, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping data in week (Monday thru Sunday) (Access 2000)

    I am using this in a query so I tried changing the 0 to a 2 in the expression. However, this returns 2 groupings for each week in the chart.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping data in week (Monday thru Sunday) (Access 2000)

    Can you attach a stripped version of your db so we can have a look at it ?
    Francois

Posting Permissions

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