Dynamic Order by

[ACase]

Hello;

I created a form which enables a user to select 1 of 3 reports and filter
them using a series of combo boxes. I now want to create an option box which
would enable him/her to sort the data by the field chosen in the option box.

My filters are created dynamically using vba code and stored into the
strWhere clause. They are then executed using:

DoCmd.OpenReport "rpt_Merchant", PrintMode, , strWhere

I now want to add the order by statement, and was hoping to add it to the
strWhere clause

strWhere = strWhere & " ORDER BY " & "Regions.Region"

This however doesn't work. Is there an easier way to go about this.

Any help would be much appreciated.

Thanks
ACase

 

[Jeff Boyce]

"doesn't work" doesn't tell us much... Do you get an error message (if so,
what does it say)? Do you get a result, but the wrong result (if so, what's
wrong about it)?

Where are you trying to add the order by clause to the strWhere string?

Can you take the resulting string, after adding the order by info, copy it
into a query design in the SQL view, and get it to run there?

Can you create a "dummy" query, view it in SQL view, and compare that to the
strWhere your code is creating?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Douglas J. Steele]

The order in which records appear in a report is strictly controlled by the
Sorting and Grouping dialog associated with the report, not by the order of
the records in the recordset.

See whether http://www.databasedev.co.uk/sorting_reports.html helps.