ORDER BY Numerical ASC

[sinistapenguin]

HI All

I'm sure this has been brought up many times before, but I need a little help ordering my recordset by numerical characters.

Basically I have ordered my Invoices by Invoice Number but I get this:

1, 10, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 11, 110 etc.

Basically I'm trying to get it in real numerical order:

1, 2, 3, 4 etc.

I'm sure there MUST be a way round this.

If anyone knows it can you let me know.

Cheers

Sinista

 

[johnwm]

Check your datatype in the database. The Order By clause will do what you want as long as trhe field is a numeric type - your stuff is sorted alphabetically, so is probably a Char or VarChar type (Text type in Access)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[traingamer]

One way is to pad them to the same length with leading zeroes.
Another is to (in Access) convert the text to numeric with CInt([InvoiceNumber]) and sort on that.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill