Ms-access – How to order the null values

ms-access

Using Access 2003

In my table column, some of the fields are null, some of the fields are number, and some of the fields are string

Table.

ID, Value

001 
002 N/A
003 01
004  
005 N/A
006 02

So on…

I want to order the table by number, string then null values,

Query,

Select ID, Value from table order by value

Expected Output

    ID, Values

    003 01
    006 02
    002 N/A
    005 N/A
    001
    004

Need Query Help

Best Answer

  SELECT * FROM  
 (SELECT *, IIF(Val(Value) > 0, 2, IIF(ISNULL(Value), 3, 1)) AS MyOrder FROM MyTable)  
Order by MyOrder

EDIT: I think this one is clear. Modified as per Robert's query ;)

SELECT * FROM MyTable
ORDER BY IIF(Val(Value) > 0, 2, IIF(ISNULL(Value), 3, 1))
Related Topic