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



--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com