[AccessD] custom sorts/groups in a report

Susan,

    For date field named CDate, in report named R_Sales, the following course of action should meet your needs -

    (a) In the sorting and grouping dialog box of the report, enter the following expression (pl watch for word wrap) as first item in first column -
    =Format([CDate],"yyyy") & Format(DatePart("ww",[CDate],Fn_WeekStart()),"00")
    Set the sort order to Ascending
    Set the group header property for this item to Yes.

    (b) Select CDate as the second item in sorting and grouping dialog box and set the sort order to Ascending

    (c) Put the following code in report's open event so as to initialize the global variable WkStart -
    Private Sub Report_Open(Cancel As Integer)
        WkStart = IIf(Len(Me.OpenArgs) > 0, Me.OpenArgs, 1)
    End Sub

    (d) In the form, put the following code in click event of the command button for opening the report -
    Private Sub CmdReport_Click()
        DoCmd.OpenReport "R_Sales",  _
                        acViewPreview, , , , TxtWkStart
    End Sub
    ' Note - TxtWkStart is the text box where the user enters his choice of starting day of the week. Default value can be set as 1 (Sunday). Validation rule >= 1 And <= 7

    (e) In the general module, code block given below may be put -
    Option Compare Database
    Option Explicit
    Public WkStart As Integer   ' Global Variable.

    Public Function Fn_WeekStart()
        Fn_WeekStart = IIf(WkStart > 0, WkStart, 1)
    End Function

    With the above measures, dynamic manipulation of start day for weeks constituting group headers should proceed smoothly without unwanted error messages.

    Note - As an alternative, the global variable WkStart can be initialized directly from the click event of command button. In such a case the procedure in open event of the report (mentioned above) should be commented out. In fact this second alternative is considered preferable.

Regards,
A.D.Tejpal
--------------
  ----- 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.