Sort Table On Column With Null Dates

[rebelfox]

I have a table with a date field loaded from three legacy number fields using makedate(YYYY,MM,DD).

In some cases there is no date so the derived date is null.

This is Ok but when I sort on the table by clicking on the column header to list either the higest date or the lowest date at the top of the table

rows, Null ndates do not show up at the top of the table when the lowest dates are at the top of the table.  Instead it only shows the lowest

real dates and the nulls still appear at the bottom of the table.

Is there a way of making the nulls appear at the top of the table when the table is sorted on the date?

.

[pokassov]

Hello!

You can use this approach:

change your field Date on :

if(isnull(Date), 'null', Date)

[rebelfox]

Gosh of course!.

Thanks.

[rebelfox]

Hmm hasn't quite worked.

The date shows 'null' for a bad date but the 'good' dates are now formatted differently.

Instead of '15/01/2015' for example I get '42022'.

On my load statement I have:-

..

..

    BRE36 As [Invoice-Settled-Month],
     BRE37 As [Invoice-Settled-Day],
     if(BRE35 = 0, 'NoDate', MakeDate(BRE35,BRE36,BRE37) ) As [Settlement-Date],
     MakeDate(BRE35,BRE36,BRE37) As [old-Settlement-Date],
     BRE38,

..

..

[pokassov]

Hi!

if(isnull(Date), 'null', Date(Date))