sort per date, today on top

[Ulrich1947]

hi to all,

anyone knows a solution to sort a date field (containing day and month
only), so that the actual day (or the nearest one to it) is always the first
in the list?

I want to sort data by season. Lets say ananas is available in the market
from 15th of november (any year) and avocado from 15th of march (any year).
so when we have the 14th of november, sorting is topped by ananas, when we
have 16th of november to 14th of march, sorting is topped by avocado.

Thanks for any ideas.

ulrich 1947

 

[Alex Dybenko]

Hi,
perhaps you can calculate number of days between today and ananas day, and
then sort by it?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Tim Ferguson]

anyone knows a solution to sort a date field (containing day and month
only), so that the actual day (or the nearest one to it) is always the
first in the list?

1) if it's a month and a day, then it's not a date, it's a somethingelse...


2) try this:

ORDER BY Format(MarketDay,"mmdd")


Hope that helps


Tim F

 

[Ulrich1947]

Hi, thanks for helping

and thanks for reminding me, that a month and a day is not a date. I
probably should have formatted the size of the field not as a date - now I
know.

However, your second recommendation worked nearly fine, at least it led me
thinking. The problem was, to sort it that way, that today ist top of the
list, not the 1st of any january. Finally I tried

ORDER BY
IIf(Val(Format([MarketDay],"mmdd"))<Val(Format(Date(),"mmdd")),Val(Format([MarketDay],"mmdd"))+1500,Val(Format([MarketDay],"mmdd")))

whereby 1500 is just a virtual figure, higher then the biggest possible
number which could result from a maximum date in a year (31. Dec. = 1231).

Thanks again

ulrich1947

 

[Tim Ferguson]

and thanks for reminding me, that a month and a day is not a date. I
probably should have formatted the size of the field not as a date -
now I know.

The best way to model a non-date is far from obvious. The other common scenario is
Month+Year but no Day.

With your combination you could try:

two integer fields: bit fiddly to validate, ugly but reasonable user interface,
very easy to do maths with (using DateSerial() function)

one integer field using a Julian Date - easiest to validate, takes some work for
any kind of user friendliness at all; date maths is again extremely simple

one string field formatted something like "mm/dd" -- much more fiddly to
validate, but obvious and intuitive for users without any programming work, sorting
and comparing is easy but any date maths starts to get fraught.

The best solution depends on how you plan to use the things. For example, you could
benchmark the three methods on the type of comparison you mention in your post
(by the way I have not tested any of these, but you should get the picture!):-

ORDER BY DateSerial(
IIf(DateSerial(Year(Date(),MonthNum, DayNum) > Date(),
Year(Date()),
Year(Date())+1),
MonthNum,
DayNum
) ASC;


ORDER BY JulianDate +
IIf(JulianDate < DatePart("y", Date()), 0, 365)

ORDER BY DateSerial(
IIf(DateSerial(
Year(Date(),
CInt(Mid(DateText,1,2)),
CInt(Mid(DateText,3,2))
) > Date(),
Year(Date()),
Year(Date())+1),
CInt(Mid(DateText,1,2)),
CInt(Mid(DateText,3,2))
);


Hope that helps


Tim F