How to order time cells in access after midnight?

Question

I am using MS Access 2007 on Windows 2010 and have an Access DB where I have to order some records by time (hours:minutes).

The values in the cells start from 5:00 AM until the 1:00 AM. The problem is that I need that the values from midnight to 1:00AM must be at the end list.

Answer 1

The solutions is to set the format in the MS access as date/hour and format hh:nn;@ (eventually hh:nn:ss;@).

If your data list in Access is not ordered as you need you can solve in this way:

1. copy/export the list in MS Excel
2. set the format in MS Excel as in the picture below (hh:mm;@)
3. correct only the values after midnight as 24:MM:SS
4. set in MS Access new format as in the second picture below (hh:nn;@)
5. copy/import new data from MS Excel

images come from my regional version of MS Access and MS Excel

Answer 2

I would suggest making a query of your table with a calculated column which is just the time column minus 1 hour. Then you can sort based on this calculated column and the 1:00am times will all sort to the bottom. The sorting column does not need to be displayed in any of your forms or reports, just used for the sort.

Answer 3

Create a Query with the following SQL:

SELECT * FROM myTable ORDER BY TimeValue(DateAdd("h",-1,[myTime]));

In the above, change myTable to the name of your table, and myTime to the name of the field containing your time value.