i found the following to compare number in the comparerows on listbox.
my question is if i have field for dates (DD/MM/YYYY) and i want to able sort properly by year first then month and then my day. What should i do??
Select Case column
Case 2 // Number column
If Val(Me.Cell(row1, column)) < Val(Me.Cell(row2, column)) Then
result = -1
ElseIf Val(Me.Cell(row1, column)) > Val(Me.Cell(row2, column)) Then
result = 1
Else
result = 0
END IF
Return True
Case 3 //Dates column
????
End Select
What he said. You can do a string comparison on SQLDates without having to convert them to Date objects first. That works because SQLDate is in the format YYYY-MM-DD, and that will always sort correctly.
If you started with Date objects, you could also store those in the CellTag and compare them in CompareRows.
before i have a additional column in my query for the listbox that change the date field (dd/mm/yyyy) to sqldate format (yyyy-mm-dd).
all the client need to do is sorted that sqldate format column. i thought i can do without the extra column.
[quote=66375:@Richard Duke]before i have a additional column in my query for the listbox that change the date field (dd/mm/yyyy) to sqldate format (yyyy-mm-dd).
all the client need to do is sorted that sqldate format column. i thought i can do without the extra column.[/quote]
You can. If you want you can parse the input date string and convert it to SQLDate String format and compare that in the event. Or as suggested when a date is entered you an do a conversion once and store the result in the CellTag, and compare celltag values instead.
this is what i did… i like the idea about cell tag… where do i set the cell tag say column 13?
Dim theDate1 as New Date,theDate2 as New Date
DIM converted1 as Boolean, converted2 as Boolean
converted1 = ParseDate(Me.Cell(row1, column), theDate1)
converted2 = ParseDate(Me.Cell(row2, column), theDate2)
IF theDate1.SQLDate < theDate2.SQLDate THEN
result = -1
ELSEIF theDate1.SQLDate > theDate2.SQLDate THEN
result = 1
Else
result = 0
END IF
Return True
When you show the SQLDate or SQLDateTime in the listbox, you can sort these values directly in CompareRows, since they will sort correctly - though they are strings. Of course this doesn’t work when you show the dates formatted as “dd.mm.yyyy” or “mm/dd/yy”. But as long as the order is year-month-day-hours-minutes-seconds you can safely sort the string representations of the dates.
Alternatively you could set the date as RowTag (after each AddRow with: lbx.RowTag(lbx.LastIndex = theDate) and then use the dates in CompareRows. From the docs:
// Fill the listbox
lbx.AddRow(..., ..., ..., theDate.ShortDate)
lbx.CellTag(lbx.LastIndex, 3) = theDate
// CellTextPaint event
Function CompareRows(row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer) As Boolean
Select Case column
Case 0
...
Case 1
...
Case 2
...
Case 3
Dim d1 As Date = Me.CellTag(row1, column)
Dim d2 As Date = Me.CellTag(row2, column)
If d1 < d2 Then
result = -1
ElseIf d1 > d2 Then
result = 1
Else
result = 0
End if
End
Return True
End
You have to understand what a CellTag is. It’s merely a place for you to store information in the row of a ListBox that isn’t visible to the user. Because it’s a variant, it can be any information you’d like.
Yes, that’s a good way to look at it, except you can have more CellTag columns than there are visible columns. So for example, if your ListBox is defined as 3 columns, it is not an error to do something like lb.CellTag( row, 25 ) = someData. How you use the CellTags is entirely up to you.
The limitation is that a cell will only store a string, whereas a CellTag will let you store anything. Your method is similar to how we were forced to do it before CellTags were introduced.