[AccessD] custom sorts/groups in a report

Susan,

    Did you happen to try out the solution given by me ? It makes use of the existing date field in report's record source and is not dependent upon the form having to remain open. No special alteration needed for the source query and no parameters to contend with.

    If due to any reason, the value of starting day of the week is not passed to report's OpenArgs, default value of 1 (Sunday) is assumed and there is no error situation.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Susan Harkins 
  To: 'Access Developers discussion and problem solving' 
  Sent: Friday, April 23, 2004 18:38
  Subject: RE: [AccessD] custom sorts/groups in a report


  Thank you Ken. ;) 

  My final solution is to refer to the form value in the query. This time
  around, I hid the report, forcing the user to use the form to open the
  report, but that isn't the best way to do it. Unfortunately, I didn't know
  how to ward off the dread parameter prompt when the user opens the report
  naturally instead of through the form. I've no problem setting a default for
  the sort, but couldn't stop that stupid prompt because it's coming from the
  query. :( 

  Susan H. 

  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.