Dynamically Control Report Sorting 2

[StumblingThrough]

Is there a way to dynamically control how the data on a report is sorted based on the contents of a form control? I have tried building IF statements into the report's record source (SQL), but the resulting code is very long (due to the number of options in the control box). Does anyone know a better way?

 

[dhookom]

Consider at list box [lboField] with numbers and titles of fields:
1,"First Name",2,"Last Name",3,"City",4,"Zip Code"
Assume the form name is frmA
In your query, create a calculated column:
SortBy:Choose(Forms!frmA!lboField, [FirstName], [LastName], [City], [ZipCode])
Your report can then be set to sort on the column SortBy.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[StumblingThrough]

The data seems to sort correctly when I run the bound query, but the report still sorts by the data in the leftmost column. I assume on the form the bound column is 1 where the autonumber field is 1 and the data field is 2.

 

[dhookom]

UPSDeveloper,
Generally the sort order in the report is independent from the sort order in the query. You have to set the sorting and grouping in the report.

I should have mentioned the list box has two columns and the bound column is the first. The column widths should be set to 0";1"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[StumblingThrough]

Duane

I must be missing something then. If I manually set the sorting / grouping in the report, how can I change this dynamically from a form control?

What I've done to the report record source (which is a query), is to add a field as stated above, thinking the sortating order in the query would control the sorting order in the report if nothing was specified in the report Sorting and Grouping dialog box.

You seem to be saying above that I need to specify something in the Report sorting and grouping dialog box. Correct?

 

[dhookom]

Your report should now have a field "SortBy" in its field list. Set your sorting and grouping to this calculated column from your query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[StumblingThrough]

Duane,

Duh..Of course...Sorry. Now it works great. Thanks once again for your quality assistance.