Adding up Hours

message from Jackie on 27 May 2004

I need to track the number of hours a student accumulates
while taking various courses. These hours will then need
to be added, by query, to produce a total number of hours.

My problem is that I can't use a number field because then
I'll get results like 6:53 + 1:27 = 7:80 and not 8:20.

How do I get access to recognize that anything over 60
minutes needs to be added to the hours? I've tried
changing the format of a date/time field to h:nn but that
doesn't work either.

Thanks.

Douglas J. Steele replied to Jackie on 27 May 2004

The recommended approach is to store the number of minutes as a long
integer, and write your own functions to convert from total minutes to h:nn
and vice versa.

One thing to be aware of is that the Date/Time type in Access is intended to
store timestamps: complete date/time records, not just a time. Internally,
they're 8 byte floating point numbers, where the integer part represents the
date as the number of days relative to 30 Dec, 1899, and the decimal part
represents the time as a fraction of day. Access will let you assign 6:53 to
a time field (it'll store it as 0.286805555556), but look what happens when
you format that field:

?Format$(#6:53#, "yyyy/mm/dd hh:nn:ss")
1899/12/30 06:53:00

If you add 6:53 together 4 times, look what happens:

?#6:53# + #6:53# + #6:53# + #6:53#
1899/12/31 03:32:00

Archived message: Adding up Hours (MS Access Error Message)