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

Question

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.

Answer 1

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;

Answer 2

Are you sure those values are NULL rather than zero-length values? If they are, then you should replace them with NULL then set Allow Zero Length to false for the column or add a Validation Rule or CHECK constraint to do the same.

If they are NULL then this may be a bug. I assume you are using Jet 4.0, for which NULL collation should sort NULL to the end of the resultset. In which case, you need the workaround (i.e. currently selected answer).

A note on Nulls/ZLS: Access 2003 (or maybe it was 2002, which I barely ever used) changed the default in its table designer from AllowZLS: No to AllowZLS: Yes. It's very, very annoying.