Sorting by time starting from a specified time.

[Zeppi]

Hello everyone,

I have a table containing an autonumber set as the primery key, a date
field (format as 1 January 2008), a time field (format as 16:00) and various
other data. The date field is the operational day of our business and the
time is entered in rounded hours... Example

1223 1 January 2008 08:00 Other data.....
1224 1 January 2008 09:00 Other data.....

Now when i creat reports based on the above data layout and try to sort the
data order by Date and then Time, the time starts from 00:00 and ends at
23:00.

I would like to order the data 1st by Date, then by Time but starting from
08:00 and not 00:00.

That is the time order would be as following:

08:00
09:00
10:00
11:00
12:00....

.....22:00
23:00

Any help please?

 

[Zeppi]

An Edit to my original post:

The time order i need is as follows:

Start Time 08:00
09:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00
00:00
01:00
02:00
03:00
04:00
05:00
06:00
End time 07:00

Any help please?

 

[Douglas J. Steele]

Realistically, you should be storing Date and Time as a single field, the
way Access intended it to be...

Based on your sample data in the first post, am I to assume that your time
of, say, 06:00 below will actually be for 2 Jan, 2008, not 1 Jan, 2008?

The easiest approach would probably be to create a calculated field in your
query that adds the existing date and time together, then subtracts 8 hours
from it. You'd then sort on that calculated field. Date/Time values are
simply numbers, so the calculation would be:

DateAdd("h", -8, MyDateField + MyTimeField)

 

[Zeppi]

Yes the 06:00 is of the next day. As for using the date and time as a single
field would not work on the kind of business we use. Our operational day is
spread during 2 days technically, it starts on day 1 and 08:00 and ends on
day 2 07:00.

I need to keep the ours as round figures and the data is not entered on the
spot.

I will try out your method.

Adding to the above, the times are set in a table with a idcode and the
rounded time (so the user can choose it from a drop down table) i could join
the code to the table and sort it by that.

Will try it out too. Ta for the info. Will post my results later.

 

[Zeppi]

Adding to the above, the times are set in a table with a idcode and the
rounded time (so the user can choose it from a drop down table) i could join
the code to the table and sort it by that.

Will try it out too. Ta for the info. Will post my results later.

I ended up creating a relationship between the table with the reading times
and the data table and joined them in the query... then i used the idcode of
the reading time to sort. It worked great.

Thanks anyways for you comment mate.