Null Value sorting

[Alejo]

Hi Everyone,

The sorting of Date in Ascending order displays the null value at the top.
As per my project requirements, in ascending order the null values should display at the bottom?
Can someone help me on how to do it?

 

[theDBguy]

You can create a query that sorts your data properly and then use it for your form. To sort the empty dates at the bottom, you'll have to replace them with a future date. For example:

...ORDER BY Nz(DateField,#12/31/9999#)

Hope that helps...

 

[Alejo]

You can create a query that sorts your data properly and then use it for your form. To sort the empty dates at the bottom, you'll have to replace them with a future date. For example:

...ORDER BY Nz(DateField,#12/31/9999#)

Hope that helps...

Hi DBguy,

In my project, the empty values indicates that the product has not yet delivered. once the product is delivered the empty values will be updated with the date of delivery.

I did that option already by replacing the empty dates by future dates just to display it at the bottom part, however it creates confusion to the user. So if possible I prefer to display the empty value at the bottom part as empty.

 

[theDBguy]

Hi DBguy,

In my project, the empty values indicates that the product has not yet delivered. once the product is delivered the empty values will be updated with the date of delivery.

I did that option already by replacing the empty dates by future dates just to display it at the bottom part, however it creates confusion to the user. So if possible I prefer to display the empty value at the bottom part as empty.

Hi. The sample code I gave you will not replace the empty dates with anything. They should still show up as empty on your form. Give it a shot and let us know what happens.

 

[Pat Hartman]

Add a second field to the query and use it for sorting. That way, the second field can contain the actual dates and the future date for nulls. The user will just see the actual field and so will not be confused.

 

[Alejo]

Hi DBguy and Pat Hartman,

I tried the suggestion of DBguy, thank you it works.

For this particular query I have not tried the suggestion of Pat Hartman, however it gives me an idea on how to deal with other task for my project.


Thank you once again to both of you, appreciated.

 

[Alejo]

You can create a query that sorts your data properly and then use it for your form. To sort the empty dates at the bottom, you'll have to replace them with a future date. For example:

...ORDER BY Nz(DateField,#12/31/9999#)

Hope that helps...

Hi DBguy,

I thought everything is fine as I see that empty fields are at the bottom with the below codes.

SELECT Table1.ID, Table1.Date, Table1.Type, Table1.Caculated, Nz([Caculated],#12/31/9999#) AS Expr1
FROM Table1
ORDER BY Nz([Caculated],#12/31/9999#);

I noticed that if the month are same it follows the ascending order but when other months are mixed it did not follow the ascending order as below:

Note that the calculated field is Date/Time type
Can you please check

 

[theDBguy]

Hi DBguy,

I thought everything is fine as I see that empty fields are at the bottom with the below codes.

SELECT Table1.ID, Table1.Date, Table1.Type, Table1.Caculated, Nz([Caculated],#12/31/9999#) AS Expr1
FROM Table1
ORDER BY Nz([Caculated],#12/31/9999#);

I noticed that if the month are same it follows the ascending order but when other months are mixed it did not follow the ascending order as below:

View attachment 92559
Note that the calculated field is Date/Time type
Can you please check

You could try either one of these:

...ORDER BY CDate(Nz([Calculated],#12/31/9999#))

or

...ORDER BY IsNull([Calculated]) DESC, [Calculated]

Hope that helps...

 

[Alejo]

Hi DBguy,

Tried option 1 but not working as well as option 2.

At below, asking for Calculated parameter and empty field display at top with the date not in ascending order.

 

[theDBguy]

Sorry, it looks like the name of your field is Caculated. I thought it was Calculated.

Anyway, please use the correct name of your field.

 

[Alejo]

hi DBguy,

Thank you it works.. I corrected the typo in the field name.
I did not notice it as well

 

[theDBguy]

hi DBguy,

Thank you it works.. I corrected the typo in the field name.
I did not notice it as well

Okay. Glad to hear you got it to work. Cheers!