[AccessD] custom sorts/groups in a report

I considered the first, but skipped it because the article was already so
long, but I didn't actually apply it. My reasons for wanting the report to
open without error is so the user could avoid the form altogether and depend
on defaults within the report itself when the form isn't present, but it
wasn't worth the extra space for the current article. 

I started out with a function, but my goal was to do it with as few pieces
as possible.  

Susan H. 

Susan,

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

  In the reports OnOpen event, you can check for the existence of the form
and

1. Open it and switch focus to it.
2. Cancel the report with a message box to the user telling them that they
need to open the report through the form.
3. Open the form hidden, stuff a default value, and then continue.

 or

4. Change the query to use a function to get the value for the parameter
from a global variable, which could be set from the form or from the
report's OnOpen event if the form is not open.

Jim
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan Harkins
Sent: Friday, April 23, 2004 9:09 AM
To: 'Access Developers discussion and problem solving'
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.



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

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


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