Access SQL ORDER BY putting NULL values last in query

[jacjacjac]

Hello,

NOTE: Im working in MS Access 2003, only results in this SQL/database (not mysyl, MSSQL etc)

I want to be able to ORDER an SQL query and put the NULL values last.

I have a basic databse:
Table name: PeopleTable
Field names: TableID, PersonField, PersonID

The aim is to order by PersonID and put the NULL values last

I use the orderby SQL statement

SELECT * FROM PeopleTable ORDER BY PersonID;

and it shows the results ordered by the

PersonID however the NULL values are first.

The desired result is to have similar results to the above query but ensure the NULL values are displaed last?

How can i achieve this is MS Access 2003? Can someone provide me with the SQL using the said example?

Thanks,

Jac

[Jerry Maiapu]

Just add the keyword "Desc" to sort the Id from highest to null like this:

SELECT * FROM PeopleTable ORDER BY PersonID DESC;

[Rabbit]

Or if you want the same order but just with the nulls at the end, order by the Nz() function result of the field where the second parameter is all Z's or something similar.

[NeoPa]

No option to comment when setting Best Answer, but that hits the nail squarely on the head. An example, in case it's needed, would be :

SELECT   *
FROM     [PeopleTable]
ORDER BY Nz([PersonID],'zzzzz')