Sometimes I like to explain why something works so bizzarely. Date/Time fields are an excellent example.
The whole problem with date/time fields in Access can be summed up simply. A data/time field is an "interpretation" (sometimes called a "cast") on another field type. A date-time field is the DOUBLE (floating) number of days since the reference date. It is not actually a time, but rather is the length (in days) of the time-line from the reference date to the targeted time. If I recall correctly, we are in the upper 30,000s on the reference date at the moment. The exact number isn't important - the principle is the thing.
When you store a date/time in an Access table, you REALLY stored a DOUBLE timeline value. Differences between two date/time fields correctly show the number of days and fractions thereof between the two items - because that difference concept is a natural property of lines of any sort, time or otherwise. So the question is, why does the system not translate it correctly? Because when you show Access a time field formed by differences and tell it to format the result - it isn't a time-line number any more. (It is a difference-line number.) And the formatting routines expect timeline numbers.
So what's a programmer to do? Write a couple of formatting routines that convert the number to the units you REALLY wanted to work in. Work in those numbers. Write a couple of formatting routines that do the reverse formatting job. Keep them in your module library as things you might need to use frequently. Just remember, the units are days and fractions of a day.
Oh, don't worry about the precision. For a DOUBLE, you have 64 bits of which perhaps 55 bits are in the mantissa. If you do the math, this is 15 decimal digits plus a little extra. You are using five digits for the days. That leaves 10 digits for the time fraction, and there are less than 100,000 seconds in a day. (86,400 to be exact). So that means all time-line numbers probably have time at least to the millisecond included if they were derived from the system clock directly. Of course, for differences of two numbers that are relatively close, watch out for catastrophic cancellation to eat some of your extra precision. But normally, you should have no trouble if you realize what your TRUE units are.
Besides, Access, like most Microsoft products, has a lot of trouble if you go deeper than seconds anyway. From my mainframe, when I try to copy data measured from a really fast clock, I always have to strip away seconds or Access thinks my dates are all weird strings. So you only have to do the math a couple of times to get it all right.
For converting total time to hours, minutes, and second, here's one way that might work. Take the field formed by subracting one time from another as a number of seconds. HINT: See DATEDIFF function with units "s"
Now divide that number using MOD 60 to get seconds, divide again using MOD 60 to get minutes, and the quotient is hours. With integers for seconds and minutes and a LONG for hours, you can measure times in the millennia.
Going the other way, add hours * 3600 + minutes * 60 + seconds and then divide the result by 86400. It is back to days/fractions again.
