Passing Order By Parameter Not Working

[cp2599]

I want to be able to change the sort order of a report by the user
selecting the preferred order on a form. I referenced that form field
in the order by clause of the query but the sort does not work. If I
take the value of the field on the form and place it in the order by
clause of the query it works fine. Is the order by unable to accept
parameters?

 

[Klatuu]

Only if you build the SQL for the report's record source manually.
Reports don't always use the ORDER BY. To set the order of a report, you
should use the report's sorting and grouping.

You can do this programatically.

First, set up your group with any of the sort choice you want, probably the
most common. Then you can either pass the value of the control where you
select the sort order to the report using the OpenArgs argument of the
OpenReport method or you can directly reference the value of the control.

In either case, set the group based on the value. For example purposes, I
will assume the value is in a combo box on the form:


Me.GroupLevel(1).ControlSource = Forms!MyForm!MySortCombo

 

[vanderghast]

Note that a field name, neither a table name, cannot be a parameter.


You can order by on a parameter, but it would be rather useless since a
parameter will be seen as a constant (not as a field NAME), and ordering by
a constant does not give much.

On the other hand, ORDER BY can work on computed expression, so, maybe not
very efficient, but if an ad hoc query cannot be used, and if ordering AFTER
having produce the reocrdset is also out of question, then:

ORDER BY CHOOSE( param_int, fiedl1, field2, field3, field4)


will order by field1, if param_int = 1, by field2 if the param_int =2, ...
since CHOOSE is simply a VBA function (see help for more details) which
returns a value, and the order by can then be 'tied' to the field.


Vanderghast, Access MVP

 

[vanderghast]

And as mentioned by Klatuu, the reports make their own order by anyhow, so
doing it in the query on which the report will be based is not a good idea.


Vanderghast, Access MVP