Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date field in a table. The requirement is for the records that have no dates to appear at the top of the list. Sorting by Ascending or Descending leaves the blank dates at the bottom. Is there a Sort expression I can use? I can think of other work arounds but a sort expression would be a neater solution.
Have you tried Orderby, on the date field?
Thanks but Interbase only alows Asc & Desc in SQL which I can already do within the QV 'Sort by' expression combo box. I think MySQL may allow it. Ideally I would like an expression that will place Null values at the top of a list of dates.
When retrieving from ODBC-source or a .txt-file would suggest to use the NullDisplay-functionality
<h4>SET NULLDISPLAY=<sym>;</h4>
The symbol <sym> will substitute all NULL values from the ODBC data source on the lowest level of data input. <sym> may be any string.
Use #N/A as symbol and it will be sorted as first/last and can be selected
HTH
Peter
Interbase doesn't have NULLDISPLAY in it's SQL language.
Had to resort to using an If statement on a pre load and sorting by Numeric Value. Would have thought there would be a solution by using the 'Sort by' expression in the table properties but no one has come forward.
If
(IsNull(DUE_DATE), 'Nil',date(DUE_DATE,'DD/MM/YY')) as DueDate,
When you intend to use the script on 64-bit-server, you should test the expression not with IsNull, but check on the length of the string:
LEN(Mydate) = 0 ....
HTH
Peter
Thanks Peter
We're not on 64 bit servers yet but useful to know.
Dave
Wrong post. Error in browser
Wrong post. Error in browser
Have you tried an Expression in sorting?
Try this expression:
Normal 0 false false false LV X-NONE X-NONE MicrosoftInternetExplorer4 =If(Len(Trim(DUE_DATE))=0,1)
It helds empty values in DUE_DATE field as 1.
Usin Ascending sort empty dates will be on top
If you want to use Descending sort, then change 1 to 99999999, then empty values also will be on top