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
Else
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
https://documentation.xojo.com/api/data_types/datetime.html#datetime-fromstring
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.