Sort ListView 1

[tekomni]

I am looking for an easy way to sort a list which contains numbers and dates and sort them ascending/descending. Anyone found an easy way to do this?

 

[NeilFrank]

ListViews have a SortOrder Property (lvwAscending and lvwDescending)

 

[tekomni]

Yes but it does not sort numbers and dates correctly by default.

 

[NeilFrank]

You're right, sorry!

If you are using a RecordSet to populate your ListView you can take advantage of its Sort Property:

'Set up ListView
Private Sub SetUpListViewLittle()
If gblnDoApFlowDebug = True Then Print #100, "AppointReview.SetUpListViewBig"
With lvwLittle
Dim sngWidth As Single
sngWidth = .Width
.ColumnHeaders.Clear
.ColumnHeaders.Add , , "Name", 0.42 * sngWidth
.ColumnHeaders.Add , , "Date", 0.38 * sngWidth 'SubItem(1)
.ColumnHeaders.Add , , "Time", 0.2 * sngWidth 'SubItem(2)
.Width = (.ColumnHeaders(1).Width + .ColumnHeaders(2).Width + .ColumnHeaders(3).Width) * 1.05
.Left = (fraLVs.Width - .Width) / 2 '5/10/03
.View = lvwReport
End With

End Sub


'Fill ListView
Public Sub PopulatelvwLittle(strSort As String) '5/4/03
If gblnDoApFlowDebug = True Then Print #100, "AppointReview.PopulatelvwLittle"
Dim intLine As Integer
lvwBig.Visible = False: lvwLittle.ListItems.Clear
lvwLittle.ColumnHeaders(3).Text = "Time"
With grsAppointments
.Filter = mstrFilter
If .RecordCount > 0 Then
.Sort = strSort
.MoveFirst
Do While Not .EOF
intLine = intLine + 1
Set mAppointmentListItem = lvwLittle.ListItems.Add(, "K" & intLine)
Select Case mintSelect
Case 2
lvwLittle.ColumnHeaders(1).Text = "Patient": lvwLittle.ColumnHeaders(2).Text = "Date"
lvwLittle.ListItems(intLine).Text = gcollPtIDToName(!PatientID)
lvwLittle.ListItems(intLine).SubItems(1) = Format(!ApptDate, gstrDateFormat)
Case 3
lvwLittle.ColumnHeaders(1).Text = "Provider": lvwLittle.ColumnHeaders(2).Text = "Date"
lvwLittle.ListItems(intLine).Text = gcollProviderIDToName(!ProviderID)
lvwLittle.ListItems(intLine).SubItems(1) = Format(!ApptDate, gstrDateFormat)
Case 4
lvwLittle.ColumnHeaders(1).Text = "Provider": lvwLittle.ColumnHeaders(2).Text = "Patient"
lvwLittle.ListItems(intLine).Text = gcollProviderIDToName(!ProviderID)
lvwLittle.ListItems(intLine).SubItems(1) = gcollPtIDToName(!PatientID)
End Select
lvwLittle.ListItems(intLine).SubItems(2) = Format(!ApptTime, "h:mm AM/PM&quot
.MoveNext
Loop
End If
End With
If lvwLittle.ListItems.Count = 0 Then
fraLVs.Visible = False
Else
fraLVs.Visible = True: lvwLittle.Visible = True
End If
DisplayRightButtons (2)

End Sub


'Clicking on column header sorts ListView
Private Sub lvwLittle_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
If gblnDoApFlowDebug = True Then Print #100, "AppointReview.lvwLitle_ColumnClick"
Dim strSort As String
Select Case ColumnHeader
Case "Provider": strSort = "ProviderID, ApptDate, ApptTime, PatientID" 'Provider
Case "Patient": strSort = "PatientID, ApptDate, ApptTime, ProviderID" 'Pt
Case "Date": strSort = "ApptDate, ApptTime, ProviderID, PatientID" 'Date
Case "Time": strSort = "ApptTime, ApptDate, ProviderID, PatientID" 'Time
End Select
PopulatelvwLittle (strSort)

End Sub

 

[tekomni]

Is that sorting by the recordset query? If so, that will not work in my case as the data is not fed in on a column click. Thank you for your help though

 

[NeilFrank]

How do you fill your ListView?

 

[tekomni]

Maybe I should have been more specific. It does fill from a record set when the user opens that screen but i do not wish to re-query the server for the record set. I just want to sort the data they already have.

 

[gpzcrasher]

This is a solution I use. In this example columns 3, 4 and 5 contain numberic data and the rest character data.


Private Sub lsvCutNumbers_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

With lsvCutNumbers

If ColumnHeader.Index >= 3 And ColumnHeader.Index <= 5 Then
For lngCount1 = 1 To .ListItems.Count
.ListItems(lngCount1).SubItems(ColumnHeader.Index - 1) = Format(.ListItems(lngCount1).SubItems(ColumnHeader.Index - 1), &quot;000000000&quot
Next lngCount1
End If

If .SortKey = ColumnHeader.Index - 1 Then
If .SortOrder = lvwAscending Then
.SortOrder = lvwDescending
Else
.SortOrder = lvwAscending
End If
Else
.SortKey = ColumnHeader.Index - 1
End If

If ColumnHeader.Index >= 3 And ColumnHeader.Index <= 5 Then
For lngCount1 = 1 To .ListItems.Count
.ListItems(lngCount1).SubItems(ColumnHeader.Index - 1) = CLng(.ListItems(lngCount1).SubItems(ColumnHeader.Index - 1))
Next lngCount1
End If

If .ListItems.Count > 0 Then
.SelectedItem = .ListItems(1)
End If

End With

End Sub

Hope this helps.

--------------------------------
Codito, ergo sum

 

[CCLINT]

tekomni
There are a couple of methods, but this may be the easiest, if there are only a few number/date columns:

1. When you add the elements and sub elements, add an additional block of sub elements at the end, which duplicates those columns.
These additional columns are hidden, and when added to the list, are formated as such:

Numbers: Format$(NumFld, &quot;0000.0000&quot or similar, depending the number type and how many places are needed.

Dates: Format(DataFld, &quot;yyyy-MM-dd&quot

When a user click a certain date field, you sort on the related HIDDEN date field.

Make sure the values are changed in both fields when the User, or the code, edits an element.

Another solution is to use this MS Kb article: Q170884

And yet another involves using a disconnected recordset and it's Sort method, and a hidden Sub Element column...

 

[strongm]

thread222-578008

 

[CCLINT]

I didn't quickly find a solution here so I posted the similar Kb article solution.

 

[strongm]

Sure. As I think I say in the referenced thread, my code is based on an MVPS example which in turn is based on the MS KB article (actually, it may be the other way around; the MS article may be derived from the MVPS code). However, both examples are flawed, in that they only work properly with a small number of items in the ListView, and the code in the referenced thread theoretically fixes this shortcoming

 

[tekomni]

Thanks for your help. I used a mixture of the suggestions. I added a column at runtime, set the subitems to equal the item that was clicked formatted like 0000000.00, then sort the list by the new column then remove that column. Here is the code:

Public Sub SortList(ByRef myList As ListView, myColumn As Integer)

Dim column As Integer
Dim i As Integer
Dim lst As ListItem

column = myColumn - 1

'CHECK IF NUMBER
If myList.ListItems.Count > 0 Then

If Len(myList.ListItems(1).ListSubItems(myColumn).Text) > 3 And Val(myList.ListItems(1).ListSubItems(myColumn).Text) > 0 Then

If left(Right(myList.ListItems(1).ListSubItems(myColumn).Text, 3), 1) = &quot;.&quot; Then

'ADD A HIDDEN COLUMN
myList.ColumnHeaders.Add , , &quot;&quot;, 0
'SET THE SORT COLUMN = THE NEWLY CREATED COLUMN
column = myList.ColumnHeaders.Count - 1

For i = 1 To myList.ListItems.Count

Set lst = myList.ListItems(i)
'ADD ITEMS TO NEW COLUMN FORMATTED APPROPRIATELY
lst.ListSubItems.Add , , Format(myList.ListItems(i).ListSubItems(myColumn).Text, &quot;000000.00&quot

Next i

End If

End If

End If

'SET SORT KEY
myList.SortKey = column

If myList.SortOrder = lvwDescending Then

myList.SortOrder = lvwAscending

Else

myList.SortOrder = lvwDescending

End If

'SORT THE LIST
myList.Sorted = True

'REMOVE THE NEW COLUMN
myList.ColumnHeaders.Remove (myList.ColumnHeaders.Count)

End Sub

 

[tekomni]

My last post was buggy here is the sub i created for sorting number list views:

Public Sub SortList(ByRef myList As ListView, myColumn As Integer)

Dim column As Integer
Dim i As Integer
Dim lst As ListItem
Dim boolAddedColumn As Boolean

column = myColumn - 1
' Stop
'CHECK IF NUMBER
If myList.ListItems.Count > 0 Then

'FOR SUBITEMS
If column > 0 Then

If Len(myList.ListItems(1).ListSubItems(column).Text) > 3 And Val(myList.ListItems(1).ListSubItems(column).Text) > 0 Then

If left(Right(myList.ListItems(1).ListSubItems(column).Text, 3), 1) = &quot;.&quot; Then

'ADD A HIDDEN COLUMN
myList.ColumnHeaders.Add , , &quot;&quot;, 0

boolAddedColumn = True

For i = 1 To myList.ListItems.Count

Set lst = myList.ListItems(i)

'ADD ITEMS TO NEW COLUMN FORMATTED APPROPRIATELY
lst.ListSubItems.Add , , Format(myList.ListItems(i).ListSubItems(column).Text, &quot;000000.00&quot

Next i

End If

End If

Else 'FOR LIST ITEMS

If Len(myList.ListItems(1).Text) > 3 And Val(myList.ListItems(1).Text) > 0 Then

If left(Right(myList.ListItems(1).Text, 3), 1) = &quot;.&quot; Then

'ADD A HIDDEN COLUMN
myList.ColumnHeaders.Add , , &quot;&quot;, 0

boolAddedColumn = True

For i = 1 To myList.ListItems.Count + 1

Set lst = myList.ListItems(i)

'ADD ITEMS TO NEW COLUMN FORMATTED APPROPRIATELY
lst.ListSubItems.Add , , Format(myList.ListItems(i).Text, &quot;000000.00&quot

Next i

End If

End If

End If

End If

'SET SORT KEY
If boolAddedColumn = True Then

myList.SortKey = myList.ColumnHeaders.Count - 1

Else

myList.SortKey = column

End If

If myList.SortOrder = lvwDescending Then

myList.SortOrder = lvwAscending

Else

myList.SortOrder = lvwDescending

End If

'SORT THE LIST
myList.Sorted = True

'REMOVE THE NEW COLUMN
If boolAddedColumn = True Then

' For i = 1 To myList.ListItems.Count
'
' myList.ListItems(i).ListSubItems.Remove (myList.ColumnHeaders.Count - 1)
'
' Next i

'myList.ColumnHeaders.Remove (myList.ColumnHeaders.Count)

End If

End Sub