sorting numbers

[jgallen]

I'm having a problem with sorting numbers in a database.

here's an example..

I have 4 numbers... 1, 3, 24, 6

when I sort it comes out:
1
24
3
6

how do I sort it so it'll look like:
1
3
6
24

right now I'm just doing a standard Order By field

Thanks
jga

[glenngv]

The datatype of the field should be numeric so that data are treated as numbers not strings.

[Bullschmidt]

To sort a text field which is really filled with numbers by number so that 3 shows up before 200:

strSQL = "SELECT InvNum FROM tblInv ORDER BY CLng(InvNum)"

[glenngv]

You mixed SQL function with VBScript function.
The SQL must be:

strSQL = "SELECT InvNum FROM tblInv ORDER BY CONVERT(InvNum, int)"

[Bullschmidt]

You mixed SQL function with VBScript function.
The SQL must be...

Well I actually tested my answer before posting so I can tell you that it does work at least with an Access database.

And when I tried your answer with Access it produced this error message:
Undefined function 'CONVERT' in expression.

So my guess is that my answer works for Access and perhaps yours works for SQL Server. :)

[ez4me2c3d]

Just so you know, Access uses the JetSQL (http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_list.html) language.