Order by Date

  • Thread starter Thread starter Warrio
  • Start date Start date
W

Warrio

Hello!!

I have to select from a table a date field ordered in ascending way.



The following query works perfectly :

Select myDate from myTable order by DateField



But if some records contained in the field (myDate) are null, they appear at
the beginning.

Is it possible to have the query result starting with the earliest date then
the latest and finally the empty onces?



Thanks for any suggestion
 
Hi,

Yes.




I assume you also want to know how? :-)


SELECT myDate
FROM myTable
ORDER BY Not(DateField Is Null), DateField


True is considered as -1, False as 0. So, we order by all the true( true
that datefield is NOT null, and among those, accordingly to DateField),
next, we get all those that are false.


Another more portable alternative, not relaying on true=-1, false=0:


ORDER BY Nz(DateField, #1-1-3000#)


which replaces a null by a date far in the future.


The morality of the story: you are not limited to a field, you are not
limited to field; you can order by on more than one field, or expression.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,
You could use a bit of trickery to accomplish this.
Have a calculated field in your query that inserts a date far in the future
if myDate is null.

SELECT myTable.myDate
FROM myTable
ORDER BY IIf([myDate] Is Null,#12/30/2310#,[myDate]);
 
Thannks a lot, but I have to display the result, and I can't display the
date 2310, so I will have to hide it with another condition again!

the solution given by Michel works great! (... ORDER BY Not(DateField Is
Null), DateField)


Dan Artuso said:
Hi,
You could use a bit of trickery to accomplish this.
Have a calculated field in your query that inserts a date far in the future
if myDate is null.

SELECT myTable.myDate
FROM myTable
ORDER BY IIf([myDate] Is Null,#12/30/2310#,[myDate]);

--
HTH
Dan Artuso, Access MVP


Hello!!

I have to select from a table a date field ordered in ascending way.



The following query works perfectly :

Select myDate from myTable order by DateField



But if some records contained in the field (myDate) are null, they appear at
the beginning.

Is it possible to have the query result starting with the earliest date then
the latest and finally the empty onces?



Thanks for any suggestion
Click to expand...
Click to expand...
 
nice sense of humour by the way !!

That's really great! I knew that we could order by more than one field, but
didn't try to order by a conditon!
Thanks again !

Best regards
 
Although the problem is already solved, you could achieve
equal solution by performing a union query, where the
first section, you query "Where MyDate is not null" (or
something to that extent), and the second section you
specify the converse.

That way, you're not hedging your bets (relative to
Michel's post) that the True statement evaluates to a
lower number than the false statement (true = -1, false =
0); however, Michel's solution is very likely have a
faster execution time, if that is a consideration.

Warren
(Not an MVP)
 
Hi,
The query I provided does not display the date 2310, it only displays your myDateField
with the nulls at the end.

--
HTH
Dan Artuso, Access MVP


Warrio said:
Thannks a lot, but I have to display the result, and I can't display the
date 2310, so I will have to hide it with another condition again!

the solution given by Michel works great! (... ORDER BY Not(DateField Is
Null), DateField)


Dan Artuso said:
Hi,
You could use a bit of trickery to accomplish this.
Have a calculated field in your query that inserts a date far in the future
if myDate is null.

SELECT myTable.myDate
FROM myTable
ORDER BY IIf([myDate] Is Null,#12/30/2310#,[myDate]);

--
HTH
Dan Artuso, Access MVP


Hello!!

I have to select from a table a date field ordered in ascending way.



The following query works perfectly :

Select myDate from myTable order by DateField



But if some records contained in the field (myDate) are null, they appear at
the beginning.

Is it possible to have the query result starting with the earliest date then
the latest and finally the empty onces?



Thanks for any suggestion
Click to expand...
Click to expand...
Click to expand...
 
Back
Top