Sorting Numbers Correctly

[JithuAccess]

Hi,

Could you guys please let me know how do I sort Numbers correctly? In my List Box there is a field File Number and it's value is coming from a query. Though I have sorted the File Numbers in the Query, it's not displaying correctly.

In this above scenario I want to display the File Number 0140-2 just below 0140-1 but it's showing after 0140-19.

Data Type of File Number is Short Text as there are few texts too in the File Number.

Thanks

 

[Minty]

That's because it's text not a number. They are sorted alphabetically.
If your file number suffixes never go above 99 you could insert a leading zero after the dash -09 instead of -9 and it would then sort correctly.

 

[The_Doc_Man]

Basically, you cannot do this as you approached it. You have a TWO-part sort but only one field. You have to split the field into "left-side of the dash" and "right-side of the dash" and sort them as a two-field sort. THEN you can recombine the fields to display them however you want. But you cannot do what you want in a single sort.

As Minty says, that "number dash number" column is text and will sort in the way of text rather than numbers. What you are doing won't work as-is.

 

[MarkK]

You could...
1) replace the hyphen with a period
2) test if result IsNumeric(), and if so return result, if not return zero (or 9999999)
3) convert to single, and use that as your primary sort, and use the original field as the secondary sort.
...but obviously easier to store numeric data as a number and then apply formatting later, rather than store formatted and then have to extract a number.

 

[The_Doc_Man]

SELECT [File Number] 
FROM file-number-source 
ORDER BY 
    CLng( Left( [File Number], InStr( 1, [File Number], '-' ) - 1 ) ), 
    CLng( Right( [File Number], Len( [File Number] ) - Instr( 1, [File Number], '-' ) ) ) ;

There are other ways to skin this cat, but this or something very close to it might work for you.

Basically, use the dash as a way to split apart the number into its component parts and sort them as ordinary numbers. The only field in the SELECT clause is the original. The versions of that field in the ORDER BY clause are not displayed.

 

[Pat Hartman]

Could you guys please let me know how do I sort Numbers correctly? In my List Box there is a field File Number and it's value is coming from a query. Though I have sorted the File Numbers in the Query, it's not displaying correctly.

Easy. These are not numbers. They are strings. Strings sort character by character, left to right. Numbers are logically alligned at the decimal point and zero filled. So logically if the max length of the numbers in a list is 3, then all values shorter than three are right aligned ans zero filled.
001
002
020
041
100
rather than
1
100
2
20
41

The best solution is to break the field into its atomic parts and deal with each separately.

 

[CJ_London]

the problem with splitting the string is you lose the use of indexing - won't be noticeable on smaller datasets, but will on large ones

better to have two numeric fields, one for each part, then combine them for display purposes only

replace the hyphen with a period

don't think that would work

1.1
1.11
1.12
1.2

OP wants (I think)
1.1
1.2
1.11
1.12

so you would need
1.01
1.02
1.11
1.12

In this above scenario I want to display the File Number 0140-2 just below 0140-1 but it's showing after 0140-19.

suggest provide a more complete example of the final result required - where do you want 1-11, 1-12 to appear?

 

[GaP42]

However -

Data Type of File Number is Short Text as there are few texts too in the File Number.

The file number, in some instances, includes actual text? in which part?
If it is in the second part, and you are unwilling to change / convert to just numeric values then you will not be able to sort it as you wish, unless perhaps there are specific rules for what text is used.

 

[sonic8]

In this above scenario I want to display the File Number 0140-2 just below 0140-1

If none of the previous suggestions are applicable to your scenario, you could use the natural numerals sorting algorithm from the Windows API.

 

[gemma-the-husky]

To do it with strings, you need to get the strings to a standard length, which means storing leading zeros. So if your numbers are 4,2 you need to store

0145-02, not 145-2 or even 0145-2.

Alternatively, use 2 fields, so the 145 is in one field, and 2 is in another, and then numerical sorts will work. You can display the combined field to match the underlying sort order.