Dynamic Order by

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ACase - 14 Dec 2005 18:46 GMT

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

Reply to this Message

Jeff Boyce - 14 Dec 2005 19:15 GMT

"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>

> Hello;
>
[quoted text clipped - 20 lines]
> Thanks
> ACase

Reply to this Message

Douglas J. Steele - 14 Dec 2005 19:26 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hello;
>
[quoted text clipped - 20 lines]
> Thanks
> ACase

Reply to this Message