Dynamically sorting a report

[Guest]

I have a report with two groups: Territory and Agent; and 4 fields like:
Client, Sold(Y/N), Average Days to Sold and Premium.
Users want to filter the report by Territory and Agent. (I have no problem
here. Users will be able to select a territory or an agent from two drop down
boxes in a control form, then open the report that contains only selected
territory or agent.)
Then they want to sort the selected records by Sold, or Avg Days to Sold, or
Premium. This is the problem.

I have read all related articles in this forum.
First I used allen’s code:
Select Case Forms!frmChooseSort!grpSort
Case 1 'Sold
Me.GroupLevel(0).ControlSource = "Territory"
Me.GroupLevel(1).ControlSource = "Agent"
Me.GroupLevel(2).ControlSource = "Sold"
Case 2 'Premium
Me.GroupLevel(0).ControlSource = "Territory"
Me.GroupLevel(1).ControlSource = "Agent"
Me.GroupLevel(2).ControlSource = "Premium"
Case 3 'Avg Days To Sold
Me.GroupLevel(0).ControlSource = "Territory"
Me.GroupLevel(1).ControlSource = "Agent"
Me.GroupLevel(2).ControlSource = "AvgDaysToSold"

End Select

I appended the above code to the open View of the report, which has the
following codes already:

If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = ""
End If

Then I added “Territory, Agent and Forms!frmChooseSort!grpSort†to the
Sorting and Grouping Dialogue in the report Design View.

However, when I select a territory and premium from the drop down boxes, and
click Preview command, I get the following message:
“You must define a sort field or expression for the group header or footer
in the report you tried to previewâ€

I have no idea what is going on. Help please!

 

[Marshall Barton]

I have a report with two groups: Territory and Agent; and 4 fields like:
Client, Sold(Y/N), Average Days to Sold and Premium.
Users want to filter the report by Territory and Agent. (I have no problem
here. Users will be able to select a territory or an agent from two drop down
boxes in a control form, then open the report that contains only selected
territory or agent.)
Then they want to sort the selected records by Sold, or Avg Days to Sold, or
Premium. This is the problem.

I have read all related articles in this forum.
First I used allen’s code:
Select Case Forms!frmChooseSort!grpSort
Case 1 'Sold
Me.GroupLevel(0).ControlSource = "Territory"
Me.GroupLevel(1).ControlSource = "Agent"
Me.GroupLevel(2).ControlSource = "Sold"
Case 2 'Premium
Me.GroupLevel(0).ControlSource = "Territory"
Me.GroupLevel(1).ControlSource = "Agent"
Me.GroupLevel(2).ControlSource = "Premium"
Case 3 'Avg Days To Sold
Me.GroupLevel(0).ControlSource = "Territory"
Me.GroupLevel(1).ControlSource = "Agent"
Me.GroupLevel(2).ControlSource = "AvgDaysToSold"

End Select

I appended the above code to the open View of the report, which has the
following codes already:

If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = ""
End If

Then I added “Territory, Agent and Forms!frmChooseSort!grpSort” to the
Sorting and Grouping Dialogue in the report Design View.

However, when I select a territory and premium from the drop down boxes, and
click Preview command, I get the following message:
“You must define a sort field or expression for the group header or footer
in the report you tried to preview”

I've never seen that message, but maybe it's caused by the
strange grouping field. Try setting the grouping (in design
view) to:

Territory
Agent
Sold

 

[Alex Dybenko]

Hi,
you can try to use technique from this sample:
http://www.pointltd.com/Downloads/Details.asp?dlID=45