Null after not null value ...

[zrzr]

I have a table named folder, with a number a description, and what I need is to sort folders by the number and description.
The problem is that there are some records with no number, and these records must be at the end of the list, but with SQL, NULL is before other numbers.

I can't use UNION query, cause description is a Memo field and UNION queries don't suppor it.

So, what can I do ???

 

[jrbarnett]

If you don't need to display the numbers on the query, you can do the following:

Select Nz(Number, "zzzz&quot As Number, description
from folder
order by 1

This will put zzzz in the number field where it doesn't exist, and as zzzz comes after 9, this will sort your problem.

John

 

[zrzr]

Good idea, but I need to display the number .

But, in the form I can do iif([Number]<>&quot;zzzz&quot;;[Number];&quot;&quot
and the job is done !!!

Thanks a lot John !!

 

[zrzr]

Much better :

Select Number, Description
From folder
order by Nz([Folder].[Number],99999)

 

[jrbarnett]

Zrzr,

My reason for using zzzz is because there is no way it could come up in a numeric field - whereas your 99999 could possibly.

John

 

[zrzr]

Yes, I had understood.

But in my case, the number is a code :

two numbers for the year, and three for the number of the folder. So, ther will be no problem until the 999th folder in year 999 !!!

What is much better is to put Nz function on ORDER BY. With this method, the number isn't modified, and it's no use to modifie it again in the form !!!