Order By Help

[lauritz]

Hi,

I want to order my recordset by a character type field that contains either "N/A" or integer values. When I sort it (asc) to get the lowest hours remaining for the currently selected object I get weird results.

for instance "10000" is less than "1250"! Am I missing something? Any help greatly appreciated.

 

[alvechurchdata]

for instance "10000" is less than "1250"!

You're sorting alphabetically which means Access looks at each character in turn and ignores the length of the word. You're sorting words not numbers.

Think "comes before" rather than "is less than". "111111" comes before "2" in the same way that "AAAAAA" comes before "B".

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lauritz]

Thanks Geoff. Any suggestions as to how to get around it??

 

[mp9]

It's clunky and it uses IIf (which I don't like becuase it can be slow) but this works:

SELECT YourTableName.YourField
FROM YourTableName
ORDER BY IIf(IsNumeric([YourField]),CLng([YourField]),Null);

http://www22.brinkster.com/accessory/

 

[PHV]

ORDER BY Val([your field]), [your field]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alvechurchdata]

Another option is to use Format$(myField, "000000") to pad with leading zeroes. Sorting "011111" and "000002" works as you'd expect it to work.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lauritz]

Thanks to everyone that helped here. Much appreciated.
It turned out the the solution that best fitted was Geoff's idea of using format$.

Thanks again .