Help Needed with Strange Sorting Order

[WickedElm]

Hi there...I have a report that needs to group a set of records based on a "Status" field...the status can be either "R", "Y", or "G"...i have set this field as a group but the problem is that I need it to display the groups in the order of R, Y, then G...i am not sure how to do this through code or otherwise since it is not in ascending/descending order of the alphabet....ANY help would be greatly appreciated...

Thanks!

Brian

 

[LarryDeLaruelle]

Brian:

If you are using a query as the source of the report, add a column to the report and add this to the field cell:

SortOrder: IIf([Status] = "R", 1, IIF([Status] = "Y", 2, 3))

This will create a sort order/grouping field you can use in the report to get the order you want. Just use the SortOrder as your grouping criteria and add the status field to the group header band. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[cmmrfrds]

Build a sort key and then sort on it. Either create a function to build the key or use iif statements.

Select Status, SortStatus(Status) as sortkey
From table
Order by Sortkey

Function SortStatus(sts As String) As String
Select Case sts
Case "R"
SortStatus = "1"
Case "Y"
SortStatus = "2"
Case "G"
SortStatus = "3"
Case Else
SortStatus = "4"
End Select
End Function

 

[WickedElm]

Thanks for the help everyone!!! I ended up using Larry's advice and it worked like a snap!!!

 

[deduct]

Just be aware that Larry's method only tests for "R" and "Y", so by any chance any of the remaining records are not "G" they will be mixed in with the "G" records, perhaps giving you unexpected results in the third grouping.