Programmatically change Sort/Group field?

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

I have designed a report where filter parameters are obtained from a form.
Sometimes the user wants it sorted by one field, sometimes by another.
I am using Sorting and Grouping to sort by this field, and both fields show
up in the group header.
Rather than create the report twice, one with field A as the group sort and
the other with field B, is there a way to programmatically change this, the
way I am changing the filtering depending on their input on the form?
Mich
 
In your query that feeds the report you can create a SortOption field in
several ways.
The easiest is a calculated field like this --
SortOption : IIF([Forms]![YourForm]![CheckBox] = -1, [Field1], [Field1])

You can nest more IIFs or use Option Group instead of check boxes.

You can also change from ascending to descending by having a second field
SortOption_1. A check box to control which field outputs data or null.
Then in the report have both fields in the one Sorting and Grouping but one
ascending and other descending. The one that has an output will result in
the sort as null will not affect sorting.
 
M said:
I have designed a report where filter parameters are obtained from a form.
Sometimes the user wants it sorted by one field, sometimes by another.
I am using Sorting and Grouping to sort by this field, and both fields show
up in the group header.
Rather than create the report twice, one with field A as the group sort and
the other with field B, is there a way to programmatically change this, the
way I am changing the filtering depending on their input on the form?
Click to expand...


You can use the report's Open event to change an existing
Sorting and grouping entry. Assuming there is a text/combo
box that contains the name of the field to sort:

Me.GroupLevel(N).ControlSource = Forms!theform.thetextbox

and the same kind of thing for the group header text box.
 
Thanks, this will help me design the same report to used for several
different ways of presenting the data.
Mich
 
Back
Top