?
Solved

VB5.0: Sorting in a List View control

Posted on 1999-11-25
2
Medium Priority
?
177 Views
Last Modified: 2010-05-19
Hi, I have got a list view control in a form.  It has about 6 columns.  All coulmns contain either a "number" , a "date" (mmm dd yyyy), or a "string".  My problem is that when I click on a column to sort that column, it treats its contents as strings and sorts it accordingly.  For example, for a number coulmn, it puts all numbers starting with a 1 first, then the 2 etc.  For my dates column, instead of going from January to December, it sorts from April to September.  How can I force my list view control to treat each column differently and then sort accordingly.

A quick response would be greatly appreciated.

Thanks
0
Comment
Question by:jych
2 Comments
 
LVL 4

Accepted Solution

by:
Juilette earned 800 total points
ID: 2234705

'CREATE SORTING OF LISTVIEW BY ALPHA, NUMBER, DATE
'
'PUT THIS IN GEN DECLARATIONS OF FORM
'*****************************************************************
'
Private Declare Function LockWindowUpdate Lib "user32" _
        (ByVal hWndLock As Long) As Long
'
Private Function InvNumber(ByVal Number As String) As String
    'This function is to deal with the negative numbers, so then they can be sorted alphabetically
'
    Static i As Integer
    For i = 1 To Len(Number)
        Select Case Mid$(Number, i, 1)
        Case "-": Mid$(Number, i, 1) = " "
        Case "0": Mid$(Number, i, 1) = "9"
        Case "1": Mid$(Number, i, 1) = "8"
        Case "2": Mid$(Number, i, 1) = "7"
        Case "3": Mid$(Number, i, 1) = "6"
        Case "4": Mid$(Number, i, 1) = "5"
        Case "5": Mid$(Number, i, 1) = "4"
        Case "6": Mid$(Number, i, 1) = "3"
        Case "7": Mid$(Number, i, 1) = "2"
        Case "8": Mid$(Number, i, 1) = "1"
        Case "9": Mid$(Number, i, 1) = "0"
        End Select
    Next
    InvNumber = Number
End Function
'
'LOAD THE LISTVIEW WITH RANDOM INFORMATION
'PUT THIS IN LOAD OR CLICK
'*********************************************************
'
Dim l As Long
    Dim dblRnd As Double
    Dim dteRnd As Date
    With ListView1
        ' Add three columns to the list - one for each data type.
        ' Note that the data type is set in the column header's
        ' tag in each case
'
        .ColumnHeaders.Add(, , "String").Tag = "STRING"
        .ColumnHeaders.Add(, , "Number").Tag = "NUMBER"
        .ColumnHeaders.Add(, , "Date").Tag = "DATE"
'
        ' Set the column alignment - has no bearing on the sorts.
'
        .ColumnHeaders(1).Alignment = lvwColumnLeft
        .ColumnHeaders(2).Alignment = lvwColumnRight
        .ColumnHeaders(3).Alignment = lvwColumnCenter
'
' Set BorderStyle property.
   ListView1.BorderStyle = ccFixedSingle
'
' Set View property to Report.
   ListView1.View = lvwReport
'
        ' Populate the list with data
        With .ListItems
            For l = 1 To 100
                With .Add(, , "ListItem " & Format(l, "0000"))
                    dblRnd = (Rnd() * 10000) - 5000
                    dteRnd = (Rnd() * 1000) + Date
                    .ListSubItems.Add , , Format(dblRnd, "0.00")
                    .ListSubItems.Add , , Format(dteRnd, _
                    "dd/mm/yyyy")
                End With
            Next l
        End With
    End With
'
'PUT THIS IN THE COLUMNCLICK EVENT OF LISTVIEW
'***********************************************
    Dim lngStart As Long
    Dim lngCursor As Long
    Dim l As Long
    Dim strFormat As String
    Dim strData() As String
    Dim lngIndex As Long
    On Error Resume Next
   
    With ListView1
        ' Display the hourglass cursor while list is sorting
        lngCursor = .MousePointer
        .MousePointer = vbHourglass
       
        ' Prevent the ListView control from updating on screen
        ' and also to speed up the sort
        LockWindowUpdate .hWnd
       
        ' Check the data type of the column being sorted,
        lngIndex = ColumnHeader.Index - 1
   
        Select Case UCase$(ColumnHeader.Tag)
            Case "DATE"
                ' Sort by date.
                strFormat = "YYYYMMDDHhNnSs"
                ' Loop through the values in this column. Re-format
                ' the dates so as they can be sorted alphabetically,
                With .ListItems
                    If (lngIndex > 0) Then
                        For l = 1 To .Count
                            With .Item(l).ListSubItems(lngIndex)
                                .Tag = .Text & Chr$(0) & .Tag
                                If IsDate(.Text) Then
                                    .Text = Format(CDate(.Text), _
                                    strFormat)
                                Else
                                    .Text = ""
                                End If
                            End With
                        Next l
                    Else
                        For l = 1 To .Count
                            With .Item(l)
                                .Tag = .Text & Chr$(0) & .Tag
                                If IsDate(.Text) Then
                                    .Text = Format(CDate(.Text), _
                                    strFormat)
                                Else
                                    .Text = ""
                                End If
                            End With
                        Next l
                    End If
                End With
                ' Sort the list alphabetically by this column
                .SortOrder = (.SortOrder + 1) Mod 2
                .SortKey = ColumnHeader.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 = strData(1)
                            End With
                        Next l
                    Else
                        For l = 1 To .Count
                            With .Item(l)
                                strData = Split(.Tag, Chr$(0))
                                .Text = strData(0)
                                .Tag = strData(1)
                            End With
                        Next l
                    End If
                End With
           
            Case "NUMBER"
                ' Sort Numerically
                strFormat = String(30, "0") & "." & String(30, "0")
                'Re-format the values so as they
                ' can be sorted alphabetically.
                With .ListItems
                    If (lngIndex > 0) Then
                        For l = 1 To .Count
                            With .Item(l).ListSubItems(lngIndex)
                                .Tag = .Text & Chr$(0) & .Tag
                                If IsNumeric(.Text) Then
                                    If CDbl(.Text) >= 0 Then
                                        .Text = Format(CDbl(.Text), _
                                        strFormat)
                                    Else
                                        .Text = "&" & InvNumber( _
                                        Format(0 - CDbl(.Text), _
                                        strFormat))
                                    End If
                                Else
                                    .Text = ""
                                End If
                            End With
                        Next l
                    Else
                        For l = 1 To .Count
                            With .Item(l)
                                .Tag = .Text & Chr$(0) & .Tag
                                If IsNumeric(.Text) Then
                                    If CDbl(.Text) >= 0 Then
                                        .Text = Format(CDbl(.Text), _
                                        strFormat)
                                    Else
                                        .Text = "&" & InvNumber( _
                                        Format(0 - CDbl(.Text), _
                                        strFormat))
                                    End If
                                Else
                                    .Text = ""
                                End If
                            End With
                        Next l
                    End If
                End With
                ' Sort the list alphabetically by this column
                .SortOrder = (.SortOrder + 1) Mod 2
                .SortKey = ColumnHeader.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 = strData(1)
                            End With
                        Next l
                    Else
                        For l = 1 To .Count
                            With .Item(l)
                                strData = Split(.Tag, Chr$(0))
                                .Text = strData(0)
                                .Tag = strData(1)
                            End With
                        Next l
                    End If
                End With
            Case Else
                'Just sort it alphabetically
                .SortOrder = (.SortOrder + 1) Mod 2
                .SortKey = ColumnHeader.Index - 1
                .Sorted = True
        End Select
        ' Unlock the list window so that the OCX can update it
        LockWindowUpdate 0&
        ' Restore the previous cursor
        .MousePointer = lngCursor
    End With
0
 

Author Comment

by:jych
ID: 2234719
Thanks a bunch
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question