Solved

How do you get a listview control to sort by date properly?

Posted on 2004-08-24
13
329 Views
Last Modified: 2008-01-09
I am programming in VB 6 using the ListView control.  When trying to sort on date columns unexpected results occur.  Does anyone have some insight on making dates sort properly either ascending or descending?  


Thank you in advance
D
0
Comment
Question by:John Gates,
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +5
13 Comments
 
LVL 19

Accepted Solution

by:
Shauli earned 400 total points
ID: 11888430
you will have to modify this code to suit your needs. The sortlistview sub should be in a module. Then you'll have to modify the column click event of the listview, and to add a tag to the spesific column while you load the listview with data. last, you'll have to add an extra column:


'in module
Public Sub SortListView(ByRef oListView As MSComctlLib.ListView, _
                        ByRef oColumnHeader As MSComctlLib.ColumnHeader)
Dim oListItem As MSComctlLib.ListItem
Dim i As Integer
Dim iTempColIndex As Integer
Dim bNoTagInColumn As Boolean
   
    With oListView
        If .ListItems.Count < 2 Then GoTo Exit_Point
            iTempColIndex = .ColumnHeaders.Count - 1
        If oColumnHeader.Index = 1 Then
            For i = 1 To .ListItems.Count
                Set oListItem = .ListItems(i)
                oListItem.ListSubItems(iTempColIndex) = oListItem.Tag
            Next
            If Len(Trim(oListItem.Tag)) = 0 Then bNoTagInColumn = True
        Else
            For i = 1 To .ListItems.Count
                Set oListItem = .ListItems(i)
                oListItem.ListSubItems(iTempColIndex) = oListItem.ListSubItems(oColumnHeader.Index - 1).Tag
            Next
            If Len(Trim(oListItem.ListSubItems(iTempColIndex))) = 0 Then bNoTagInColumn = True
        End If
       
        If bNoTagInColumn Then
            .SortKey = oColumnHeader.Index - 1
        Else
            .SortKey = iTempColIndex
        End If
       
        If .SortOrder = lvwAscending Then
            .SortOrder = lvwDescending
        Else
            .SortOrder = lvwAscending
        End If
       
        For i = 1 To .ListItems.Count
            Set oListItem = .ListItems(i)
            oListItem.ListSubItems(iTempColIndex) = ""
        Next
    End With
   
Exit_Point:
    Set oListItem = Nothing
End Sub

'in listview column click event
Private Sub ListView1_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
ListView1.Sorted = True
Call SortListView(ListView1, ColumnHeader)
End Sub

'where you load the listview with data
'assuming the date is in a column in the listview

  whateverlistitem.SubItems(columnnumber) = Format(!yourfield, "mm/dd/yy")   'add data to column
  whateverlistitem.ListSubItems(columnnumber).Tag = Format$(!yourfield, "yyyymmddHHMMSS")   'add  a fprmated tag
  ....
  ....
  whateverlistitem.ListSubItems.Add      'add an extra column

S
0
 
LVL 10

Expert Comment

by:anv
ID: 11889338
as such date columns should not have such problem in sorting...
i sorted my listview on a date column, and no such prob existed..

just specify the column number in the sortkey

rest if u could give the details of the error you are getting that might help

anv
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 11889340
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:_agj_
ID: 11889485
Check if the listview has only ascending and descending sort properties.

The flexgrid used to have a load of sorting techniques for different content/formats.
0
 
LVL 1

Expert Comment

by:Drackon
ID: 11889735
You can use the windows API ListView functions to create user defined sort functions.  check this out on the Microsoft website
http://msdn.microsoft.com/library/en-us/shellcc/platform/commctls/listview/messages/lvm_sortitems.asp

If your unfamiliar with the windows API check out this tutorial
http://www.hodgestephens.com/apitut.htm

Cheers,
Drackon
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 100 total points
ID: 11890029
Using API functions with callback to create custom sorts is slow if you have a large amount of data in your ListView.
I've looked at several ways of sorting numerivc and date data. The easiest way is to format the dates as ISO standard dates: i.e "yyyy-mm-dd". If you do this the in-built text sort of the ListView will work.

If you need the dates in a format other than "yyyy-mm-dd" e.g. "mm/dd/yy" you can add an additional column to the ListView but make it invisible and populate it with the date in the format "yyyy-mm-dd". When the used sorts on the formatted date column "mm/dd/yyyy" all you need to do is call the sort on the invisible column sorted "yyyy-mm-dd". This is the fastedst method of sorting items in a ListView.
0
 
LVL 17

Author Comment

by:John Gates,
ID: 11892384
Thanks everyone!  I will try these tonight and get back to you.

D
0
 
LVL 1

Expert Comment

by:LastToKnow0
ID: 11894829
You could also "cheat" by creating an additional column with 0 for its width, and reformat a text date like "Friday the 27th of August" as 20040827.  Then sort instead by the hidden column.
0
 
LVL 1

Expert Comment

by:LastToKnow0
ID: 11894868
Duh me.  Already suggested.  I skimmed over the wrong parts of other comments.
0
 
LVL 17

Author Comment

by:John Gates,
ID: 11898297
Shauli  I like your post but here is my situation.....

I need this sort to happen automatically (Without a click event).
I need it to stay applied after a refresh.

Your proposed solution requires a column click event.

Any ideas?

D
0
 
LVL 17

Author Comment

by:John Gates,
ID: 11898306
JR2003 I like your idea but again a problem.

I am getting the contents of the listview from a database.  How would I change the format when it comes in this way? or for that matter add the additional field>?

D
0
 
LVL 19

Expert Comment

by:Shauli
ID: 11898350
You can always do (in addition to the column click) something like:

"SELECT * FROM yourtable WHERE whatever ORDER BY yourdatefield Desc"

By doing that in your sql statement you load the listview and sort it by your datefield. I wrote DESC (from latest date to early), of course you can change it to ASC (from earliest to latest).

S
0
 
LVL 17

Author Comment

by:John Gates,
ID: 11898437
THanks guys you got me thinking!!!  I used the default sort in the listview control but when I read the data in applied the format directive...  For some reason the date directly out of the database would not format the date properly, but putting it through the Format depicting mm/dd/yyyy seems to do the trick!

Thanks!
D
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

739 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