Can I make sort order in a report dynamic?

[juschuma]

I have a report that I want the user to be able to run from a form and select a sort order from a combo box.

Currently I use an If statement to check which value is chosen from the combo box.

I have 3 versions of the same report:

rpt_Test_SortByX
rpt_Test_SortByY
rpt_Test_SortByZ

So, based on the if statement in the Command Button click event, I call one of the three reports.

I have to believe that there's a better way of doing this. Can I make the sort order in the report dependant on the combo box choice on the form?

One thought was to have insert the selected value from the combo box into a temp table and include that temp table as a value in the query that builds the report. Then I could select that value from the sort box in the report.

Would this work or is there a better alternative?

 

[KenReay]

Hi

In the On OPen Event of the report

Select Case Forms!MYForm!MyCombo
CAse 1
Me.OrderBY = MyField1
CAse 2
Me.OrderBy = MyField1
...etc
End Select
Me.OrderByOn = True Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[Junior1544]

Don't forget to disable any grouping or sort order in design view first or the above code wont do any thing...

Hope this helps...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[juschuma]

I need the grouping in the design view the report should look like this:

Division
Pacific
Vendor 1 $50
Vendor 2 $65
Northeast
Vendor 3 $72
Vendor 4 $35
Southeast
Vendor 5 $56
Vendor 6 $12
Vendor 7 $23

The report should be grouped by Division. That won't change. However, my sorting is either by Vendor Name or by Amount. That's the dynamic part I want.

 

[juschuma]

I got it to work. Didn't need to disable any groupings.

One last question. How can I make it sort by either ascending or descending in this example?

Thanks.

 

[sonso]

I suppose you could run a query with the ORDER BY changed and then run a report that had no internal groupings. This would save the database engine from having to resort the underlying table for the report. The question I have is how did you get the report to sort in Pacific, Northeast, Southeast order (as P comes before N in the alphabet)? Is there a lookup on a numeric sort for the region name?

 

[juschuma]

That was just a typo. It should be Northeast, Pacific, Southeast. As for the ASC and DESC, I just built that into the following command:

Me.OrderBy = Forms![frm_Results]![cbox_SortOrder] & " DESC"

 

[sonso]

I was hoping you had found a secret approach to this. I want to sort a report in Spring, Summer, Fall, Winter order!