Hi,
I have to sort a list in proper manner but I couldn't found the way, the required sorting as below
Thanks
Default Sorting Proper Sorting AB1000 AB11 AB102 AB102 AB11 AB1000
Hi,
I have to sort a list in proper manner but I couldn't found the way, the required sorting as below
Thanks
Default Sorting Proper Sorting AB1000 AB11 AB102 AB102 AB11 AB1000
The field you are trying to sort is a text string and it is correctly sorting it 'alphabetically'
If all records start with AB or with two letters then you can add a query field Mid(YourFieldName, 3) to grab the number part and sort that in ascending order.
Place that field first in your query and untick the Show checkbox
Last edited by isladogs; 05-16-2018 at 01:34 AM.
How to sort was answered, but an additional advice to avoid such problems in future.
When some field values are constructed from strings and numbers, or from numbers, but you need the string be in text format, then make all part of result string of fixed length, an preceed/end them with special characters. For numeric parts - use preceeding "0"'s. I.e. in your example field values would be 'AB1000', 'AB0102', 'AB0011', or 'AB001000', 'AB000102', 'AB000011', depending on max possible number in numeric part. In case the string part length can vary too, the values may be like 'AB1000', 'A–0102', 'B–0011' (where I used CHAR(150) to extend string part).
Edit. I assumed ASCII value of character determines the sort order, but it looks like regional settings would kick in - I couldn't find a passing non-litera character except a space to add at end of string part and to get proper sort order. Whatever I used, it did work only when used to precede the string. So only 'AB1000', 'A 0102', 'B 0011' will work
Arvil's advice is sound.
An alternative is to split the field into two: text part and number part and then create a composite index of both fields with unique values
Code:SELECT Left([MyValues],2) & Mid([MyValues],3) AS Expr1 FROM tblMyTable ORDER BY CInt(Mid([MyValues],3));
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.