Sort is not sequential

[Sky]

Hello All,

How come when I sort a group of numbers they don't sort
sequentially. For example, if I sort these numbers in
ascending order:

1,3,2,4,6,5,7,9,8,11,10,12

I would expect to get:

1,2,3,4,5,6,7,8,9,10,11,12

But I get this instead:

10,11,12,1,2,3,4,5,6,7,8,9

Is there a way to sort data like in the second example
above?

-Sky

 

[GreySky]

You must first convert the text to a number.

For example: clng([txtInfo])

In alphanumeric sorting, you get what you saw. In numeric
sorting, it works properly.

David

 

[Ron Weiner]

Cause they aint numbers. Sure they look like numbers, but they are text and
are being sorted as such. To have the data sorted as numbers you will need
to convert it data to a number before sorting. Look at functions like
Cint(), Clng(), Cdbl(), etc.

Ron W

 

[Hugh O'Neill]

Hello All,

How come when I sort a group of numbers they don't sort
sequentially. For example, if I sort these numbers in
ascending order:

1,3,2,4,6,5,7,9,8,11,10,12

I would expect to get:

1,2,3,4,5,6,7,8,9,10,11,12

But I get this instead:

10,11,12,1,2,3,4,5,6,7,8,9

Is there a way to sort data like in the second example
above?

-Sky

The 'numbers' are being treated as text by Access rather than as
numeric values. If these are values in your Table, change the Field
type to Number.

hth

Hugh

 

[Marshall Barton]

I looked into suggestions from all and in the course of
trying each one, but then I found something in the help
section. You guys are all corect about the numbers being
treated as text, and the help file said to use a function
called "Val()".

Sure enough, the numbers sorted correctly. Help section
also said to try adding a leading zero to numbers below
10, i.e. "01, 02, 03" ect. I opted for the Val() function
instead. Either way will work though.

Thanks to all who responded. You guys rock!!!

The reason the guys that rock suggested using something
other than Val() is that Val does several other things
beside convert text digits to a numeric value. If the text
data contains characters that can not be converted, it just
converts the left most part that can be converted, e.g.
Val("12x3y45z6") returns a 12. OTOH, if the text contains
things that can in some way be interpreted as a number it
will convert it, e.g. Val("1d-2") returns 0.01.