Listbox column ordering

when i click on the head of a listbox it auto orders the column in this case date in the form of mm/dd/yy
in asending order.
except if i add a date with the year 2022 or 22
it put the higher date 22 at the top instead of at the end of the list.
how can i get the list to reorder properly.
example screen shots below

Implement the CompareRows event to provide your own comparison code.

Function CompareRows(Row1 as Integer, Row2 as Integer, Column as Integer, ByRef Result as Integer) As Boolean
  Dim d1 As Date = Me.RowTag(Row1) ' assuming the date is stored as the row tag
  Dim d2 As Date = Me.RowTag(Row2) ' assuming the date is stored as the row tag
  If d1.TotalSeconds > d2.TotalSeconds Then
      result = 1
      Return True
  ElseIf d1.TotalSeconds < d2.TotalSeconds Then
      result = -1
      Return True
      result = 0
      Return True
  End If
End Function
1 Like

Don’t sort on the column content, sort on something sortable (such as seconds) and store that in the rowtag. You need to do the sorting yourself.

manually sort
forgot to mention the Date column is one of columns i am sorting on.
rowtag would allow me to sort only on one column

when i click on the header ( date ) it does sort on content and has been doing so for a while, except recently it is sorting a higher year out of place.
why would it work in some cases

instead of seconds how about converting date mm/dd/yy to yymmdd
and sort on that ??

Use a celltag then.

I have dates in a listbox column, but how the date looks (dd/mm/yy vs. mm/dd/yy, etc) is a presentation aspect and users should be able to choose that. By storing the SecondsSince1970 equivalent in a celltag, you can sort correctly regardless of the user’s presentation choice.

1 Like

If you have any dates that are not in the same leading year digits range, I suggest yyyymmdd instead of just yymmdd. The main thing is to implement your own CompareRows() event and then select use something other than the visible cell contents. That may be seconds; it may be yyyymmdd; it may be anything that will sort correctly. Store that in a rowtag or celltag or object property or whatever suits your use case.

If you need to sort on multiple columns, nest those where the result = 0 would otherwise occur.

Forgot to address this. The most obvious case where it would appear to work in the past and not now, is if in the past all the dates were within the same year and you always displayed in mm/dd/yy format. As Tim notes, you really don’t want the presentation format to break the sort. Aside from Y2K-like issues, just skipping leading digits (e.g. m/d/yy) would not work nor the dd-mm-yy format common in much of the world.

In your example post, all the visible entries were within a single year and sorted with leading zeros. That breaks as soon as you have multiple years or do not have leading zeros, or display as dd/mm/yy instead of mm/dd/yy, etc.

without celltag you could make a date from string, check the column number in the event CompareRow
and call a compare method

Function DateCompare(value1 As Date, value2 As Date) As Integer
  // ... add code if value is Nil
  If value1.SecondsFrom1970 > value2.SecondsFrom1970 Then Return 1
  If value1.SecondsFrom1970 < value2.SecondsFrom1970 Then Return -1
  Return 0
End Function

I put the sqldate (2021-10-23) in the celltag and then in the compare rows sort on the celltag. I have a couple of routines that display the date, usually in this format: Oct 23, 2021. My users find this a lot easier to read.