Null Date sort to bottom Query Problem

[lilmosweet]

Hello, I'm new to this but I have a challenging Database Project. I need to sort the DUE_DATE in Asc order grouped by each Status Code sort but the null values in DUE_DATE have to be at the bottom. I have everything done except I cannot get the null DUE_DATE to sort to the bottom of each Status Code group. Can anyone help? I thought I solved this earlier with nz([DUE_DATE],0) but this did not work.

Thanks,
Michele

 

[jjturner]

Welcome Michele!

Considering your dual requirement (ASC order on values, but remaining Nulls tacked onto the end), you've essentially asked Access to contradict itself since Nulls are sorted to the top in Ascending order.

In order to coerce your intended result, we'll need to contrive something. What I'm thinking is that you'll need to create a new calculated field which assigns an arbitrary value to Null DUE_DATE such as 1, and likewise, for any populated DUE_DATE assign the value 0 (or anything less than the arbitrary value of 1), i.e.:

NullSorter: IIf([DUE_DATE] Is Null, 1, 0)

Then hierarchically, you would sort on this calculated field "NullSorter", then on your "DUE_DATE" field.

That is my first thought, but I wouldn't be surprised if someone has something a bit more clever up their sleeve to offer . . .

Hope this helps.

Regards,
John

 

[lilmosweet]

Thanks - I'll give it try. It sounds right and I was wondering about the 1 & 0 and how to use it. I welcome all responses since I have tried everything I could think of today

 

[lilmosweet]

This did not work however I know that 0 is the null value and 1 is for not null. Is there a way to sort the null value dates to the bottom of the ascending DUE_DATE order in my query? This is the last part of my database that I need to complete.

 

[jjturner]

Ok - and just for the sake of clarification:

Sort fields should be arranged in this order in your query design grid:

1. [Status Code]
2. NullSorter
3. DUE_DATE

And you needn't display the "NullSorter" field (check box un-checked) in your results - it just needs to be inserted between your other 2 fields in the Ascending sort order hierarchy in the query design grid.

Also, the 'immediate If' function ("IIf") has 3 arguments - the criteria, the value if True, and the value if False - so that is how you would interpret the formula I suggested.

With respect to the arbitrary values of 0 & 1, "NullSorter" results that calculate to '0' values will sort ahead of anything with '1' values. That will trick Access into thinking Nulls go 'after' populated DUE_DATES.

Cheers,
John

 

[lilmosweet]

It worked!!!!! Thank you John!

Michele

 

[jjturner]

And again - I'm afraid you've switched my formula to be opposite of what I was suggesting. For the calculated field ("NullSorter"), assign the value of 1 to your *Null* values. The value of 0 is for *Is Not Null*

(please ignore - per above post, you already got it running correctly)

 

[jjturner]

Great! Glad you got it working!
(I'm a bit slow with my responses - late night, I suppose )

Best wishes on your project Michele

 

[lilmosweet]

Thanks John again, yes it is up and running. I was busy building the form for the buttons for the macros - blah, blah, blah but the critical part is complete. The Status Code is grouped and they want to see the Due Dates in the Oldest to newest order with null value at the end of each grouping of Status Codes ( In Process, Open, Complete, etc...). I've been working on this project all night from home now tomorrow I can kick back and kind of relax at work LOL

Take Care and Thanks so much for your help!
Michele