Sorting Number Type

[keirnus]

Hello,

I am currently using Listview control.
The sorting feature of the control is useful.

<ListviewCtrlName>.Sorted = True 
<ListviewCtrlName>.SortKey = <FieldToSort> 
<ListviewCtrlName>.SortOrder = <SortOrder>

It sorts well with Text type
but is weird when sorting Number Type.
Ex.
(before sorting)
[1st] 3
[2nd] 2
[3rd] 11
[4th] 4
[5th] 1

(after sorting)
[1st] 1
[2nd] 11
[3rd] 2
[4th] 3
[5th] 4

The correct one should be:
[1st] 1
[2nd] 2
[3rd] 3
[4th] 4
[5th] 11

How to sort number type correctly?

 

[jzwp22]

Your "after sorting" version is typical when a number is stored in a text datatype field. To correctly sort this, you have to convert the value to a numeric value and then sort. A typical query which you could use as the row source for the list box control might go something like this:

SELECT yourtablename.yourfieldofinterest
FROM yourtablename
ORDER BY Clng(yourtablename.yourfieldofinterest)

Alternatively, you can change the datatype of your field in the underlying table to numeric.

 

[keirnus]

Your "after sorting" version is typical when a number is stored in a text datatype field. To correctly sort this, you have to convert the value to a numeric value and then sort. A typical query which you could use as the row source for the list box control might go something like this:

SELECT yourtablename.yourfieldofinterest
FROM yourtablename
ORDER BY Clng(yourtablename.yourfieldofinterest)

Alternatively, you can change the datatype of your field in the underlying table to numeric.

Thanks for the reply jzwp22.

The data type is already "Number".
But still, it isn't working.

I think the listview can't recognize if it's numeric or alpha.
Even if it could, seems like the sort function of listview is for alpha only.
I prefer to use the control's function to have a fast output.
Using a query will be my last option.

But then again, are there any better ways to sort numbers in listview control?

 

[DCrake]

Even though the control is a nuber type field when it is displayed in the list view it is treated as a string. To get around this problem add another column to your listview with a column width of 0 and set the field to be Format(YourNumberField,"00000") and when the user clicks on the number column get it sort on this new column instead.

David

 

[jzwp22]

David,

I didn't realize that the list view control would treat even a numeric field as a string--learn something new everyday! Thanks.

 

[DCrake]

Depending on the type of listview you are using some let you define the data types for individual columns. If that's the case you can define them as numeric, date, text type columns. If you do not get that functionality then all columns are treated as text irrespective of what the columns contains.

The list views I use let me define the column types and whether individual columns can be sorted.

David