How to sort ListVeiw control column by date

Recently I saw an interesting article by Ken Getz “Sort Items in a ListView Control” in Advisor magazine. Article discusses using IComparer class of .NET to make proper comparison for dates and numbers. This is certainly the right way for .NET, by what about Access/VB/VBA – what we can do here? And here we can achieve the same result with less coding!

Say you have a ListView control on a form and in Column 3 you show dates in dd/mm/yyyy format. If you click on column header in order to sort by that column – control will sort it by text representation of dates, like:

01/01/2005
01/02/2006
01/03/2007
02/01/2005
03/01/2006
04/01/2007

So, dates are not sorted at all! Now you have to add one more column to your ListView, say this will be column number 8, with zero width – so it will be invisible for users. And when you fill ListView – you have to fill this column with dates in yyyymmdd format, or for example in long representation of date – CLng(varMyDate), in other words – the way it can be sorted correctly. And finally in ListView_ColumnClick event you need to check for ColumnHeader.Index, if our date column is clicked – then you have to sort Listview on column 8:

If (ColumnHeader.Index – 1) = 3 Then
    ListView.SortKey=8
Else
    ListView.SortKey=(ColumnHeader.Index – 1)
End If
ListView.Sorted = True

Easy!