More trickier sorting and grouping

[jwaguile]

I have "tests" that are performed, each with a date and time (as part of a single datetime field). My report lists these tests, but they need to be sorted such that the most recent test appears at the top of the report (ascending order), but if there are multiple tests on a single day, the tests will be in ascending order within that day...

Example:

04/04/09 08:00:00 AM
04/04/09 09:15:00 AM
04/04/09 12:25:00 AM
04/02/09 08:00:00 AM
04/02/09 08:10:00 AM
02/02/09 06:10:00 PM
12/05/08 03:55:00 PM

Maybe a better way to phrase this is "The dates should be in descending order, but the times should be in ascending order, but I'm having trouble achieving this because the field is a single datetime field and I can't set that field to be in both ascending and descending order."

Any help would be more appreciated than vanilla ice cream!

 

[jwaguile]

Well jwaguile, while it is true that you cannot sort a report in both ascending and descending order on a single field, what you can do is make two derived fields, using the DateValue and TimeValue functions with your datetime field as an argument, and sort on those two, new, separate, derived fields.

Let's say your datetime field is called "dtDateTime", then in your report's recordsource, using the query editor, you can make two new derived fields, "DateValue(dtDateTime)" and "TimeValue(dtDateTime"). Access will defaultly name these two new fields "Expr1" and "Expr2".

You should be able to sort your report in ascending order by Expr1, and descending order by Expr2.

 

[pbaldy]

Try these in Sorting and Grouping:

=DateValue([DateTimeField])...Descending
=TimeValue([DateTimeField])...Ascending