Order by

[Guest]

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

 

[Guest]

Why not build the whole sql statement and then assign that to the Record
source of the Report. That way you can incorporate all sql options.

 

[Marshall Barton]

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

Even if you could do that, which you can't, it wouldn't work
for anything beyob a trivial report. The sorting in a
report is specified in the report's Sorting and Grouping
window (View menu).

To provide a user selectable sort field, you need to create
a sort/group level in the report's design. You can then
modify it at run time by using code in the report's Open
event:

Me.GroupLevel(N).ControlSource = Forms!yourform.sometextbox

That assumes that your form has a (hidden?)text box that
contains the name of the sort field. The N in the above
needs to be replaced with the appropriate sort/group number.
If you do not have anything else in Sorting and Grouping,
use 0.