Dynamically Control Report Sorting 1

[StumblingThrough]

I have a report that I would at times like to see sorted a number of different ways (sorted based on different fields). What is the best way to control this sorting from a form? The source for the report is currently a (totals) select query. Thanks!

 

[SteveR77]

Here is one method that should allow you to do what you desire -

Change the 'Order By' clause in a query based on user input.

There are many instances where you would like to sort a form or report on different fields depending on a user's selection. The following is an SQL example of a dynamic Order By clause.

This example assumes you have a form ('F_Emp') with an Option Group ('Sort_Option') with two possible choices. Option 1 is for sorting by employee name and option 2 is for sorting by employee number. Variable sorting can be accomplished in the Report however it is much more efficient to put this in the SQL code.
Select M_Employees.Name, M_Employees.Emp_Number, M_Employees.Address
From M_Employees
Order by
IIf(Forms!F_Emp!Sort_Option=1, M_Employees.Emp_Name, M_Employees.Emp_Number);

An alternative form of the Order By is to use ordinals, these numbers refer to the fields in the select clause. Example: ordinal 1 refers to M_Employees.Name, and ordinal 2 refers to M_Employees.Emp_Number.
IIf(Forms!F_Emp!Sort_Option=1, 1, 2);


This form of the IIf clause is very useful when you need to nest IIf clauses because of more than two sort choices.

 

[StumblingThrough]

SteveR77: Thanks for the help. When I put similar code into the reports OnOpen property, it works fine. But it will not allow me to nest IIF statements - says the expression is too complicated to be evaluated. And I'm having to use the specific field names - does not work using ordinals. Any ideas?