Solved

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

Posted on 2004-08-24
13
327 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
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 50

Expert Comment

by:Ryan Chong
ID: 11889340
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 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