As stated in other post, the listbox sorts on alpha rather than numeric so A1, A2, A3, A10, A20, A30 sorts as A1, A10,A2, A20, A3,A30.
What is the best practice, recommended method to have it sort in what we humans would call “logical” or “normal” A1,A2,A3, A10,A20,A30?
The data is loaded to the listbox either by SQLite (primarily) or by PostgreSQL. Regardless of how it is initially populated, subsequent sorts result in ‘abormal’ results.
Best practices & ideas appreciated! 2016 R1 BTW.
Tim
look at the comparerows event… there are topics on this forum with examples
[quote]
The CompareRows event is used for sorting a column of a ListBox in a manner that is not provided by the default mechanism. Row and Column are zero-based. The result of the comparison is returned in the last parameter, Result, which is declared Byref. The default mechanism sorts cell values lexicographically. If you implement the event, it gets called during a ListBox sort, e.g., when a user clicks in the header area.[/quote]
Function CompareRows( row1 As Integer, row2 As Integer, column As Integer, ByRef result As Integer ) As Boolean
dim val1 as double
dim val2 as double
dim ltr1 as string
dim ltr2 as string
//
// Assume starts with SINGLE Character, followed by 1 to n numeric characters
//
ltr1=left(cell(row1,column),1)
ltr2=left(cell(row1,column),1)
if ltr1=ltr2 then
result=0
else if ltr1>ltr2 then
result=1
else
result=(-1)
end if
if result=0 then // only check numeric part if alpha parts are equal
val1=val(mid(cell(row1,column)))
val2=val(mid(cell(row2,column)))
if val1=val2 then
result=0
else if val1>val2 then
result=1
else
result=(-1)
end if
end if
return true
End Function
Tim To really answer your question you’ll have to say more about your custom string format. Since it starts with an alpha character it cannot be treated and sorted like a normal number. Some important questions:
Will it ever start with a character other than A?
If so, Is B1 before or after A10? (Is the alpha segment more significant than the numeric segment?)
How long is the numeric portion? (More than one character?)
Is there anything that can or will follow the numeric portion? (Are there more that the two segments?)
Will the leading alpha portion ever be more than one character?
Will the numeric segment contain commas or decimal points or other punctuation or non-numeric characters?
The main issue is that alpha strings are LEFT aligned whereas numeric string need to be RIGHT aligned. So either custom rules need to be defined per your format or numeric fields need to have leading zeros added so that all numeric fields are the same length and thus inherently right aligned.
The compare rows functionality is pretty straightforward IFF you can come up with some rules that define how to compare two of your custom strings to know which one you consider greater than the other.
The scenario can always change since it is not under my control. These are names of spaces, like apartments or parking spaces, or other rental spaces.
@Dave - you gave me a LOT more than I had hoped for - THANK YOU!
A template/how to was what I was looking for. I was spinning my wheels and rather than sink deeper into the muck and mire, I thought I would expand my post/thread and hope that someone would offer the starting point that I was really needing. From there, I can go forward.
Thank you both for your time, posts and food for thought. Thank you Dave for giving much more than I had hoped for.
This code is from Dave S, but has been massaged very slightly to correct a few issues.
Thank you Dave for the GREAT starting point [maybe ending point too ]!
Dim val1 as Integer //double
Dim val2 as Integer //double
Dim ltr1 as string
Dim ltr2 as string
//
// Assume starts with SINGLE Character, followed by 1 to n numeric characters
//
ltr1 = left(Me.cell(row1,column),1)
ltr2 = left(Me.cell(row1,column),1)
If ltr1 = ltr2 then
result = 0
ElseIf ltr1 > ltr2 then
result = 1
Else
result = (-1)
End if
If result=0 then // only check numeric part if alpha parts are equal
val1=val(mid(Me.cell(row1,column),2))
val2=val(mid(Me.cell(row2,column),2))
If val1 = val2 then
result=0
ElseIf val1 > val2 then
result=1
Else
result = (-1)
End if
End if
return true
]!