Order By on a Report using a Variable (XP)

Topic

New Reply

I have a tab form with command buttons that allow the user to sort by certain fields. E.g., the user may sort by client name. The command button runs this code on the On Click event:
Me.RecordSource = “select * from [qry_recerts] where ” & sVar & ” ORDER BY [pat_lastname], [pat_firstname]”
(sVar has already been established as a variable for filtering records, e.g.,

=”B”)

Reply | Quote

Replies

I believe you need to employ the orderby and orderbyon properties of the report to affect the sort order of a report.

Reply | Quote

Reports don’t use the sort order of their recordsource, so I am not surprised your second attempt didn’t work.

But your first version looks OK.
Where did you declare the variable sOrder? I it was decalred within the code for the form then the report does not know about it.

Try msgbox(sOrder) before the me.orderby = sOrder.

You could try this in the open event for the report
me.orderby = forms!myform.orderby
me.orderbyon = true

Where myform is the form you open the report from.

Reply | Quote

On the report, go to sorting and grouping, add the field to sort on and indicate ascending or descending.

Or you can add a field for sorting purposes to the report recordset to sort on and place it in the report. Make it invisible and sort on that using the sorting and grouping feature.

HTH

Reply | Quote