Change Groups and Sorts

[A_Li_N]

I have a report that contains information on employees with grouping and sorting.
It is currently set up like this:

Group on Employee with A on top
    Group on TempStartDate with A on top
        Group on StartDate with A on top

The data needs to be grouped like that for the report to show correctly. Only problem is that the report will always show alphabetically by employee.
What I need to know is if there is any way to insert a sort before the first group via VBA...something to the effect of this:

Sort by TempStartDate with A on top
    Group on Employee with A on top
         ....

That way it will order the report by TempStartDate instead of Employee.

So is there any way to programmaticly add/remove/move those kinds of Groups/Sorts? Or do I have to have multiple copies of the report based on how I want it sorted?

Thanks for any help you can give!

 

[stopher]

You can change the sort order at runtime (in the On Open event) like this (ref):

Me.GroupLevel(0).ControlSource = "the1stSortField"
Me.GroupLevel(1).ControlSource = "the2ndSortField"

So you could perhaps have a drop down on a form for the user to choose the sort order then in the On Open event, check the drop down and apply the relevant sort order.

You can't add/remove GroupLevels at runtime but you can get round this by simply changing a sort order to one already used:

Me.GroupLevel(0).ControlSource = "the2ndSortField"
Me.GroupLevel(1).ControlSource = "the2ndSortField"

If you need to change the sort order without messing up your report levels, then just add extra sort/groups at the top of your sort/group list but without the header/footer and refer to these in your code (it doesn't matter if you repeat field names).

You can have much more control over your report by changing it a design view but there's more effort involved. See here for a starter on how you would go about it. Also take a look at the CreateGroupLevel method.

hth
Chris

 

[A_Li_N]

Thanks so very much. I will work this in tomorrow...if you don't hear back, consider the matter fixed and working. Thanks for the links!