Order by problem

[lauritz]

Hi,

I am trying to sort my record set (ascending) by a character field that contains either decimal values or "N/A".

One subset has the following values:
-29.7
-239
-199

The record I want (lowest value) is -293, yet the -29.7 appears first. I can see why but can't find a different way of doing it.

My select statement currently is ;
SELECT * from engine order by format$( hoursRemaining, "00000.0; (00000.0)")

Any help gratefully appreciated.

 

[alvechurchdata]

The problem is that Format$() is giving you a character expression and Access is sorting this field alphabetically. Try ordering by Val(hoursRemaining).

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[lauritz]

Thanks Geoff! Works a treat.