Solved

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

Posted on 2004-08-24
13
326 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
  • 4
  • 2
  • 2
  • +5
13 Comments
 
LVL 19

Accepted Solution

by:
Shauli earned 400 total points
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
0
 
LVL 7

Expert Comment

by:_agj_
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Author Comment

by:John Gates
Comment Utility
Thanks everyone!  I will try these tonight and get back to you.

D
0
 
LVL 1

Expert Comment

by:LastToKnow0
Comment Utility
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
Comment Utility
Duh me.  Already suggested.  I skimmed over the wrong parts of other comments.
0
 
LVL 17

Author Comment

by:John Gates
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now