How to display table order by code (e.g. 01, 02 ... then zero columns)?

Question

How to display table order by code (e.g. 01, 02 ... then zero columns)?

Using an Access 2003 Database

Table

Name Code Name Country Code

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

So,...,

I want to display the table serial number by code type, nation code

In my field "citizenship code" some of the columns are null, so I want to display the table code by name, country code (for example, 01, 02 ... then null columns).

My request.

Select * from the order table by national code, header code

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

But the Null value comes first in the nationality code, I want to display the nationality code such as 01, 02, 03, 05, then null values

Help is required upon request.

Answer 1

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

Please note that NZ() will only work in the Access user interface. For a more neutral approach, you can try the expression in the ORDER BY , for example. something like

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

Answer 2

Are you sure these values ​​are NULL and not null? If they are, then you must replace them with NULL, and then set Allow Zero Length to false for the column, or add a validation rule or CHECK to do the same.

If they are NULL, this may be a mistake. I assume that you are using Jet 4.0, for which NULL sorting should sort NULL before the end of the result set. In this case, you need a workaround (i.e. the currently selected answer).

A note on Nulls / ZLS: Access 2003 (or maybe 2002 that I have ever used) changed the default value in my table constructor from AllowZLS: No to AllowZLS: Yes. It is very, very annoying.