Sort Assending

[lead 27]

HI
I am just wondering if any one knows why if I have a list of numbers e.g. 1,2,11 and I do sort assending does it put them in the order of 1,11,2 rather than 1,2,11

Does anyone know how to correct this?

 

[WayneRyan]

Lead,

That happens when what you are sorting are not numbers.

If you explicitly change the field's datatype to number you'll be OK.

If you use:

Select TextNumber From YourTable Order By CLng(TextNumber)

you'll also be OK.

HTH,
Wayne

 

[lead 27]

Hi
Thanks for replying. I have text as well as a number e.g. "con wd 1" etc so could you explain abit more about how to do CLng (where to put it) please

Thanks

 

[WayneRyan]

Lead,

The CLng function converts the string representation of a number
into a pure number. Then it will sort properly.

Your data appears to not be "atomic". That is it consists of
multiple data elements per field.

There are some pretty neat things you can do in code, but it
requires much more knowledge of your data.

Is there always a number in the field?
Is it always the last "word"?

Need more info.

Wayne

 

[lead 27]

Hi
Yeah there will always be a number and yes it will always come last. the format is CON WD [NUMBER] so e.g CON WD 1

I hope this explains better
Thanks

 

[DALeffler]

If every field begins with "CON WD ", there's no reason to sort by those characters: Sort using just the characters after "CON WD ".

Try:

SortCode:Format(Mid(tMyTableFieldName, 8), "0000000000")

using a query with your table as the record source and sort the SortCode field ascending.

The limiting factor for sorting "correctly" is the arbitrarily large string of consecutive "0" characters in the Format function call.

If you need to sort with the leading characters, just concatenate the Format function results to the parsed leading charactrers:

SortCode:Mid(tMyTableFieldName, 1, 7) & Format(Mid(tMyTableFieldName, 8), "0000000000")