Sorting Issue...Please Help

[mrssevans]

I have a report that is based on an underlying query. I have a form that the user can pick which fields they would like to sort by, but it won't sort correctly on the report. It sorts in the query correctly, but not the report. Here is the syntax I have in the query.
(There are three of these that are driven by combo7, combo9, and combo10 on the user form)
IIf([Forms]![Report Sort]![Combo7]="Buyer",[Buyer],IIf([Forms]![Report Sort]![Combo7]="Feedyard",[Feedyard],IIf([Forms]![Report Sort]![Combo7]="Cattle Type",[Sex],IIf([Forms]![Report Sort]![Combo7]="Weight Group",[LBS Avg In],IIf([Forms]![Report Sort]![Combo7]="DateOut",[DateOut])))))

 

[KenHigg]

Maybe you can try to do the sorting in the report, not the query. Try right click->sorting and grouping.

???
kh

 

[The_Doc_Man]

A report sorts for one of two reasons.

1. The underlying query is sorted.

2. The report itself has been given an Order By override.

You COULD try to build a query dynamically and re-link it as the form's new recordsource, but this task is decidedly NOT for the squeamish. Heck, it isn't something I would care to try on a good day.

But it is not too bad to supply the override. The problem is that if you have report 'GroupBy' headers set up for one order-by scheme, they might not work right for an altered ordering scheme. So you would have to use VBA code to alter the order of the GroupBy entries in the properties of the report.

To change the sort order on the form,

1. Select the form and open it in Design mode.

2. Open the form's properties.

3. On the Data tab, you will see properties "Order By" and "Order By On"

Set "Order By On" to YES and enter the list of fields you want to order by. This looks like an SQL "Order By" clause without the words "Order By"

Just remember, if you are doing both GroupBy and OrderBy functions, you cannot change OrderBy and expect GroupBy functions (like headers and footers, particularly if they contain sums or counts) to work right.

I'm not sure where the GroupBy list is kept in the report other than that it is in some collection whose name escapes me. Look up Reports in the Help Files, then find Collections to see the collections that appear on a report. One of them will be 'controls' but I forget the others.

 

[mrssevans]

Thank you for the reply. Once you said that I remembered I had some code on the report open that controlled the group/sort properties. That was what was causing it. Thank you!!