Order by Time?

  • Thread starter Thread starter Philip Martin
  • Start date Start date
P

Philip Martin

I have a query, selecting some data from a table. Then I want the data to be
presented in Ascending order of TIME. The SQL is:

SELECT BookingDetail.BookingDate, CateringTable.EatingTime,
BookingDetail.NumberInGroup
FROM BookingDetail LEFT JOIN CateringTable ON BookingDetail.BookingRefID =
CateringTable.BookingRefID
WHERE
(((BookingDetail.BookingDate)=[Forms]![FrmBookingDetailSub]![BookingDate]))
ORDER BY CateringTable.EatingTime;

The field [EatingTime] display's time as 12:00:00 when the query is run, but
it just wont ORDER properly, at least not to what I was expecting. The order
will be 13:00:00 down to 00:00:00 and then onto 12:45:00 (the recorded times
are in 15 minute segments). Thus a display of records would be something
like:

13:00:00
13:45:00
14:30:00
10:15:00
10:45:00
11:00:00
12:30:00

How do I get it to display in the logical time order??

TIA,

Philip Martin.
 
Access doesn't have a Time datatype, and the Date datatype is intended to be
used as a timestamp: there's a date included in the field. Usually if all
you've input is a time, the date will be 0 (which is 30 Dec, 1899 to
Access), but it's possible that somehow you've got other dates associated
with the times and that's why it's not sorting properly.

See whether the following works:

SELECT BookingDetail.BookingDate, TimeValue(CateringTable.EatingTime),
BookingDetail.NumberInGroup
FROM BookingDetail LEFT JOIN CateringTable ON BookingDetail.BookingRefID =
CateringTable.BookingRefID
WHERE
(((BookingDetail.BookingDate)=[Forms]![FrmBookingDetailSub]![BookingDate]))
ORDER BY TimeValue(CateringTable.EatingTime);

If it does, then you've got to work on figuring out why you've got
inappropriate dates associated with your times. How do you assign values to
EatingTime? If it's using Now, then that's your problem.
 
Douglas J. Steele said:
Access doesn't have a Time datatype, and the Date datatype is intended to be
used as a timestamp: there's a date included in the field. Usually if all
you've input is a time, the date will be 0 (which is 30 Dec, 1899 to
Access), but it's possible that somehow you've got other dates associated
with the times and that's why it's not sorting properly.
Click to expand...

Thanks for the info, it still didn't work properly with the code sample, but
I've sorted the problem.

At least now understanding how Access handles the date and time, I decided
to re-configure things. The time was being obtained from a look-up table and
I was trying to handle it as 'time'. I've now altered the table to use a
sequential number field as well, with the data being 'text'. The Order on
the query works perfectly now:o)

Philip Martin.
 
Back
Top