Null to be highest value (1 Viewer)

[kevnaff]

Hi All.

I have a query that is ordered to order the [Date Out] field in descending order. The [Date Out] field is the last to be completed on a record. So until a record (job in this case) is completed, the date out has a null value, which means it shows at the bottom of the records.

I have attempted to use conditional formatting to highlight a record when the date out field is null. However this is only effective when there are fewer than 4 records, as it is located on a subform which can only show limited records.

I was hoping for the date out with a null value to show at the top of the record list.

Does anybody have any idea on how this could be done?

Thanks

 

[ebs17]

ORDER BY
   [Date Out] Is Null,
   [Date Out]

 

[The_Doc_Man]

This might ALMOST work... but [Date Out] Is Null evaluates to either TRUE or FALSE. The bad news is that Booleans are typecasts of BYTE INTEGER (Short Short Integer) and are -1 for TRUE and 0 for FALSE ... which means that FALSE sorts before TRUE because in "normal" ascending order, -1 is lower than 0. So perhaps you REALLY wanted [Date Out] Is Null Desc to flip that script.

NOTE: My comment DOES depend on using "native" Access as the back-end or as the primary DB handler. Other systems can have different sort orders if they define TRUE/FALSE differently.

 

[ebs17]

... which means that FALSE sorts before TRUE because in "normal" ascending order, -1 is lower than 0

If -1 is less than 0, then True precedes False in ascending order, and on my computer it actually does.
But even without the "[Date Out] Is Null" line, the NULL contents appear at the top in normal sorting.
In my retest I used the datatype Date for [Date Out] - which seems logical to me.

 

[June7]

ebs, OP wants dates in descending order but still have Nulls at top.
I did tests. The Doc is sort of correct.

ORDER BY [Date Out] Is Null, [Date Out] DESC

Or provide alternate value for Null:
ORDER BY CDate(Nz([Date Out], #12/31/2100#)) DESC

 

[ebs17]

descending versus ascending - I had not paid enough attention to that. Thank you.