Sorting by a grid column

[Ali Habib]

hi

I have a grid column that displays string values like 1,1+1/2,2,2+1/2 etc. The problem is in grid when I sort by this column they donot show up in the order specified above, because they are treated as strings and maybe sorted by their ASCII values. I want them to be sorted by treating them as integers so that they appear in hte sequence shown above. Any ideas that how this can be done. By the way I am using VSFlex grid.


Regards

Ali.

[dcaillouet]

One way might be to have a hidden column that has a value that represents the true sort order of the rows(1.0, 1.5, 2.0,...). When you sort the grid, don't sort on the visible representation of the amount, sort on the hidden value.

[michi]

Hi,
I created a sort function for listview, which will sort date and number properly. What I did is temporarily disable redraw, and format the column to a string. At the same time, store the original value (also a string) to Tag of each row. Then we can sort on the formatted column. In the end, restore the original string and show up.

Here is some of the codes:

===


Public Sub SortListView(ByRef lsvTheListView As MSComctlLib.ListView, _
ByVal hdrColumnHeader As MSComctlLib.ColumnHeader, _
Optional ByVal blnReraiseErrors As Boolean = False)
On Error GoTo SortListViewAdvance_Error

Dim lngCursor As Long 'Store the original mouse pointer
Dim l As Long 'Loop counter
Dim strFormat As String 'Format used for sorting
Dim lngIndex As Long 'Index of column header
Dim strData() As String 'String array used to store splited string.
'strData(0):original listitem text
Dim intTimeZoneLength 'When sorting dates, indicated the length after the date for a
'possible time zone string.
With lsvTheListView

' Display the hourglass cursor whilst sorting

lngCursor = .MousePointer
.MousePointer = vbHourglass

' Prevent the ListView control from updating on screen -
' this is to hide the changes being made to the listitems
' and also to speed up the sort

LockWindowUpdate .hwnd

' Check the data type of the column being sorted,
' and act accordingly

lngIndex = hdrColumnHeader.Index - 1
'
'If the data type of a column in the listview is Date or Number
'the data type information of the column(s) must be stored in the Tag property
'of the column header.
'For example:
'lsvCarrierList.ColumnHeaders.Add(3, , "Creation Date", 2300).Tag = GSTR_TAG_DATE_COLUMN
'
Select Case UCase(CStr(hdrColumnHeader.Tag))

'''''''''''''''''''''''''''
'Codes omitted
''''''''''''''''''''''''''

Case NUMBER_COLUMN

' Sort Numerically

strFormat = String(30, "0") & "." & String(30, "0")

' Loop through the values in this column. Re-format the values so as they
' can be sorted alphabetically, having already stored their visible
' values in the tag, along with the tag's original value

With .ListItems
If (lngIndex > 0) Then
For l = 1 To .Count
With .Item(l).ListSubItems(lngIndex)
.Tag = .Text & Chr(0) & .Tag
'Positive
If Val(.Text) >= 0 Then
.Text = Format(Val(.Text), strFormat)
'Negative
Else
.Text = InvNumber( _
Format(Val(.Text), strFormat))
End If
End With
Next l
Else
For l = 1 To .Count
With .Item(l)
.Tag = .Text & Chr(0) & .Tag
'Positive
If Val(.Text) >= 0 Then
.Text = Format(Val(.Text), strFormat)
'Negative
Else
.Text = InvNumber( _
Format(Val(.Text), strFormat))
End If
End With
Next l
End If
End With

'
' Sort the list alphabetically by this column
'
'
' If the current clicked column is the same as last,
' change the sort order only.Otherwise,default sort order
' is ascending
'
If .SortKey = hdrColumnHeader.Index - 1 Then
If .SortOrder = ListSortOrderConstants.lvwAscending Then
.SortOrder = ListSortOrderConstants.lvwDescending
Else
.SortOrder = ListSortOrderConstants.lvwAscending
End If

'
' New column clicked so set the sort column and automatically set the order
' to ascending
'
Else
.SortOrder = ListSortOrderConstants.lvwAscending
End If
.SortKey = hdrColumnHeader.Index - 1
.Sorted = True

' Restore the previous values to the 'cells' in this
' column of the list from the tags, and also restore
' the tags to their original values

With .ListItems
If (lngIndex > 0) Then
For l = 1 To .Count
With .Item(l).ListSubItems(lngIndex)
strData = Split(.Tag, Chr(0))
.Text = strData(0)
.Tag = Mid(.Tag, Len(strData(0)) + 2)
End With
Next l
Else
For l = 1 To .Count
With .Item(l)
strData = Split(.Tag, Chr(0))
.Text = strData(0)
.Tag = Mid(.Tag, Len(strData(0)) + 2)
End With
Next l
End If
End With
'''''''''''''''''''''''''''
'Codes omitted
''''''''''''''''''''''''''

===
'*=========================================================================
'* Method: InvNumber
'*
'* Purpose: Function used to enable negative numbers to be sorted
'* alphabetically by switching the characters
'*
'* Inputs: strNumber - The negative numbers string.
'* For example: "-12.345"
'*
'* Outputs: String. (For example: "-12.345" -> " 87.654")
'*
'* Error Codes: None.
'*
'*------------------------------------------------------------------------
'* Modifications: Created by Jessie Lan (April 24,2001)
'*
'*========================================================================

Private Function InvNumber(ByVal strNumber As String) As String
Dim i As Integer
For i = 1 To Len(strNumber)
Select Case Mid(strNumber, i, 1)
Case "-": Mid(strNumber, i, 1) = " "
Case "0": Mid(strNumber, i, 1) = "9"
Case "1": Mid(strNumber, i, 1) = "8"
Case "2": Mid(strNumber, i, 1) = "7"
Case "3": Mid(strNumber, i, 1) = "6"
Case "4": Mid(strNumber, i, 1) = "5"
Case "5": Mid(strNumber, i, 1) = "4"
Case "6": Mid(strNumber, i, 1) = "3"
Case "7": Mid(strNumber, i, 1) = "2"
Case "8": Mid(strNumber, i, 1) = "1"
Case "9": Mid(strNumber, i, 1) = "0"
End Select
Next


InvNumber = strNumber
End Function


====

You can rewrite it for grid. I didn't try.

Regards,

Michi