[AccessD] custom sorts/groups in a report

On 20 Apr 2004 at 7:56, Susan Harkins wrote:

> Already tried it -- didn't work. Access will not group on a calculated
> control. 
> 

Yes it will.

Try this.
1. Create a static function in a module:

Static Function FirstDayOfWeek(Optional DOW As Long = -1) As Long
Dim lngDOWStore As Long
If DOW > -1 Then
  lngDOWStore = DOW
End If
FirstDayOfWeek = lngDOWStore
End Function

2. In the Sorting Grouping Window of your report,set the first 
"Field/Expression" to "=DatePart("ww",[MyDate],FirstDayOfWeek())" and 
set Group Header  = "Yes".
Set the second  Field/Expression to "MyDate"

3. In the Group header, put a text box with a control source of
=" Week No: " & DatePart("ww",[MyDate],FirstDayOfWeek())

4. In the detail section put a textbox with a control source of 
[MyDate]

5. Put a  button and a Combobox (cboWeekday) on the form you call the 
report from.

Set the combo:
Column Count = 2
Column Widths = 0,1 (assuming you are using inches)
Row Source Type = Value List
Row Source = 
1;Sunday;2;Monday;3;Tuesday;4;Wednesday;5;Thursday;6;Friday;7;Saturday

5. In your button on_click event in the form put:

Dim lngRetVal as long
lngRetVal =  FirstDayOfWeek(cboWeekday) 
Docmd.OpenReport "MyReport",acPreview







 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.