Sort Order of Report

  • Thread starter Thread starter Ali
  • Start date Start date
A

Ali

I have a report that I would like the user to specify on which field it is
to be sorted.

Is it possible to set the sort order at runtime?

At the moment my report is based on Qty in descending order is it possible
to change this to Stockcode when the user requests the report? It is a
straight listing nothing fancy - no subreports etc

Thanks
 
Sure. In the Open event of the report set the OrderBy property. Here's an
example from one of my own apps. "frmSortClass" is the form the user uses to
specify preferences, 'lstSort' is the list box from which they choose which
field(s) to sort by, and 'chkDescending' is a check box that indicates
whether the order should be ascending or descending. "strOrderBySource" is a
string variable I use to record the selection chosen by the user, while
"strOrderByDest" is the variable I use to build-up the string that will
eventually be assigned to the OrderBy property. "lblSubTitle" is a label on
the report that describes the sort order in use.

If CurrentProject.AllForms("frmSortClass").IsLoaded Then
strOrderBySource = Forms!frmSortClass!lstSort
Select Case strOrderBySource
Case "Name (Last, First)"
If (Forms!frmSortClass!chkDescending) Then
strOrderByDest = "LastName DESC, FirstName DESC, MiddleName
DESC"
Me!lblSubTitle.Caption = "By Family Name then Given Name,
descending"
Else
strOrderByDest = "LastName, FirstName, MiddleName"
Me!lblSubTitle.Caption = "By Family Name then Given Name"
End If
Case "Name (First, Last)"
If (Forms!frmSortClass!chkDescending) Then
strOrderByDest = "FirstName DESC, MiddleName DESC, LastName
DESC"
Me!lblSubTitle.Caption = "By Given Name, then Family Name,
descending"
Else
strOrderByDest = "FirstName, MiddleName, LastName"
Me!lblSubTitle.Caption = "By Given Name then Family Name"
End If
'other Case tests edited for brevity ...
End Select
Else
strOrderByDest = "LastName, FirstName, MiddleName"
Me!lblSubTitle.Caption = "By Family Name then Given Name"
End If
Me.OrderBy = strOrderByDest
Me.OrderByOn = True
 
Thanks but I can't seem to get this to work.

Here is what I have so far:
Select Case frm!lstSortBy.ListIndex
Case 0
If (frm!chkDescending) Then
strOrderBy = "StockCode DESC"
Else
strOrderBy = "StockCode"
End If
Case 1
If (frm!chkDescending) Then
strOrderBy = "WeeksStock DESC"
Else
strOrderBy = "WeeksStock"
End If
Case 2
If (frm!chkDescending) Then
strOrderBy = "ProductClass DESC, StockCode DESC"
Else
strOrderBy = "ProductClass, StockCode"
End If
End Select
Me.OrderBy = strOrderBy
Me.OrderByOn = True

Any ideas what I am doing wrong?

Thanks
 
I find un-indented code all but impossible to read, but as far as I can tell
there is nothing wrong in the code you posted, therefore the problem must
lie elsewhere.
 
If you have a sorting and grouping level set, it will always override any
SortBy set in the report's property. Maybe the sortby works in some reports
but I think it is more robust to use the Sorting and Grouping Levels. This
method also allows group header and footer sections.
 
I see. The code I posted is designed and intended for use in reports that
don't have any sorting and grouping levels set, and works reliably under
those conditions. Ali said in his original post that the report was "a
straight listing nothing fancy - no subreports etc" and I took that to mean
no sorting and grouping levels, but of course you are right, if any sorting
and grouping levels are present they will override the OrderBy property.
 
Thanks - that's exactly why it didn't work. I had a header that wasn't
being used so once I got rid of that it worked perfectly.

I will look at the coding option for the reports that do have Group headers.
 
Back
Top