Report Orderby and Groupby in VB

[Guest]

I have a report that needs to be grouped by the same field but sorted by
different fields. The group by field must be the last sorted field.

Report 1
Order by:
Field1
Field2
Field3
Field4
Field5 (also the GroupBy field)

Report 2
Order by:
Field9
Field10
Field5 (same field as 5 above and also grouped by)

In the vb, I assume I can use the following for Report 1:

Me.OrderBy = "Field1, Field 2, Field3, Field4, Field5" using actual field
names
Me.OrderByOn = True


and for report 2
Me.OrderBy ="Field9, Field10,Field5" again using actual field names
Me.OrderBy On = True

However, how do I Group by Field5? I have a header in the report for Field5.
If I remove this header, I lose the information in that section. This Group
by must be the last sort criteria.

Thanks for any assistance.

 

[Marshall Barton]

I have a report that needs to be grouped by the same field but sorted by
different fields. The group by field must be the last sorted field.

Report 1
Order by:
Field1
Field2
Field3
Field4
Field5 (also the GroupBy field)

Report 2
Order by:
Field9
Field10
Field5 (same field as 5 above and also grouped by)

In the vb, I assume I can use the following for Report 1:

Me.OrderBy = "Field1, Field 2, Field3, Field4, Field5" using actual field
names
Me.OrderByOn = True


and for report 2
Me.OrderBy ="Field9, Field10,Field5" again using actual field names
Me.OrderBy On = True

However, how do I Group by Field5? I have a header in the report for Field5.
If I remove this header, I lose the information in that section. This Group
by must be the last sort criteria.

The OrderBy property is overridden by the Sorting and
Grouping list. Put you sort fields in Sorting and Grouping
and you should get what you want.

 

[Guest]

I'm sorry that I was not clear. I have 1 report and 1 query. This will allow
for 2 separate reports since the query can be filtered, sorted & grouped
differently. Therefore, i can not put the fields in Sorting and grouping.
Currently there isn't any sorting / grouping set on the report. (I changed
the grouping from my original post so that I could get rid of it for this
master report)

I am tring to do somthing like the following

DoCmd.OpenReport Rptname, acNormal,,FilterText
Rptname.Orderby = OrderByText

Rptname is a variable for the report name
OrderByText is a variable for the OrderBy text

However, it seems that the OrderBy code isn't read.

Thanks for any further assistance.

 

[Duane Hookom]

You would be better off, setting the sorting and grouping using the S/G
dialog. There is code at Allen Browne's site that shows how to do this
http://www.allenbrowne.com/ser-33.html.

If you insist on using the OrderBy property, you should do it in the On Open
of the report and make sure you set OrderByOn = True