[AccessD] custom sorts/groups in a report

Susan,

You can do this using only a query, without any code, OpenArgs, or
calculated controls on the report's part.

You simply need a query with four variables:
  MajorInt	- the major date interval
  MinorInt	- the minor date interval
  CurInt	- the current date's minor interval (1 - 7 for weekdays, etc)
  MaxInt	- the number minor intervals in the major interval

For the following Major/Minor groups, use these values:

  Group        MajorInt   MinorInt   MaxInt   CurInt
  =======================================================================
  Week/Day     "ww"       "w"        7	    DatePart("w", Date)
  Month/Day    "m"        "d"        31       DatePart("d", Date)
  Year/Day     "yyyy"     "y"        366      DatePart("y", Date)
  Year/Week    "yyyy"     "ww"       54       DatePart("ww", Date)

This will group by MajorInt, then MinorInt, ordering the records starting
with CurInt for each MajorInt.

Sample Query:
  (For a report, replace the Parameters with Form control references, and
replace the OrderBy with grouping by GroupMajor, GroupMinor)

  PARAMETERS MajorInt Text ( 255 ), MinorInt Text ( 255 ), CurInt Short,
MaxInt Short;
  SELECT
    DatePart([MajorInt],[DateField]) AS GroupMajor,

(DatePart([MinorInt],[DateField])+IIf(DatePart([MinorInt],[DateField])<[CurI
nt],[MaxInt],0)) AS GroupMinor,
    tblDemo.DateID,
    Format([DateField],"yyyy") AS [Year],
    Format([DateField],"mmm") AS [Month],
    DatePart("ww",[DateField]) AS Week,
    DatePart("d",[DateField]) AS DayMonth,
    Format([DateField],"ddd") AS [Day]
  FROM
    tblDemo
  ORDER BY
    DatePart([MajorInt],[DateField]),

(DatePart([MinorInt],[DateField])+IIf(DatePart([MinorInt],[DateField])<[CurI
nt],[MaxInt],0));

-Ken
  ----- Original Message -----
  From: Susan Harkins
  To: 'Access Developers discussion and problem solving'
  Sent: Sunday, April 18, 2004 04:16
  Subject: [AccessD] custom sorts/groups in a report


  I'm grouping and sorting a report on a date field. The grouping is by the
  week -- but I want it to handle a changing first day of the week variable.
I
  can't get my head around it at all.

  Passing the first day of the week around is no problem -- I just can't
seem
  to get Access to group by it. I've looked at CreateGroupLevel, but I don't
  see how that can help.

  I tried adding a DatePart expression to the Detail section and using the
  OpenArgs property to pass the first day of the week, and while the
  expression works, I can't group by it -- it's a calculated control. I
don't
  want the users manipulating the underlying query either. They choose the
  first day of the week from a simple form and the rest needs to happen
behind
  the scenes.

  Any suggestions?

  Susan H.