Ms-access – How to display a table order by code (like 01, 02… then null columns)

ms-access

How to display a table order by code (like 01, 02… then null columns)?

Using Access 2003 Database

Table

Name Title code Nationality code

Raja       05       03
Ramu       03      
Vijay      01       02
John       04       01
Roby       06  
Abilash    02       05

So on…,

I want to display a table order by title code, nationality code

In my “nationality code” field some of the columns are null, so I want to display a table order by title code, nationality code (like 01, 02… then null columns)

My query.

Select * from table order by nationality code, title code

Name      Title    Nationality
Ramu         03 
Roby         06 
John         04       01
Vijay       01       02
Raja         05       03
Abilash      02       05

But Null value is coming first in the nationality code, I want to display nationality code like 01, 02, 03, 05 then null values

Need Query Help.

Best Answer

Try using the Nz function to provide a value for NULL columns, for example ORDER BY Nz(Nationality,9999999)

Note the NZ() will only work within the Access user interface. For a more neutral approach, you could try an expression in the ORDER BY clause e.g. something like 

ORDER BY (LEN(nationality_code) > 0), nationality_code, title_code;
Related Topic