In Access Answers, Doug Steele addresses commonly asked questions from Access developers. This month, he looks at Validate input such as ZIP codes and postal codes as well as problems dealing with time values.
I’m trying to add together time values, but Access won’t let me exceed 24 hours.
The Date/Time data type in Access really isn’t intended to be used for time durations. Rather, it’s intended to be used for timestamps: specific date/times. When you save a time without a date, Access actually stores the time as if that time occurred on 30 Dec, 1899. This is because of how date/time fields are stored. A date/time is stored as an IEEE 64-bit (8-byte) floating-point number. The integer part of the number represents the date as the number of days relative to 30 Dec, 1899 (for instance, 04 Apr, 1897 is -1000, 04 Jan, 1899 is +5, and so on). The decimal part of the number represents the time as a fraction of a day (in other words, 3:00 AM is .125, 8:00 AM is .3333333, Noon is .5, and so on).
So, if you add 3:00:00 + 8:00:00 + 6:00:00 + 8:00:00, you’re adding together .125 + .333333 + .25 + .3333333, which is 1.041667. Rather than getting 25:00:00, as you’d expect, you get 31 Dec, 1899 01:00:00, as this example from the Debug window shows:
?#3:00:00# + #8:00:00# + #6:00:00# + #8:00:00# 31 Dec 1899 01:00:00
So what can you do? There are two possibilities. The preferred way—and the one I’ll concentrate on—is to use a different data type to store your times. For example, if you need resolution to the second, use a long integer, and store your times as the number of seconds (three hours becomes 10,800, eight hours is 28,800, and so on). If you add these together, your sum is going to be 90,000 seconds. All you need is a function to return that number in normal time format. Here’s that function:
Function FormatTime(TimeInSecs As Long) As String Dim lngHours As Long Dim lngMinutes As Long Dim lngSeconds As Long Dim lngRemainder As Long lngHours = TimeInSecs \ 3600 lngRemainder = TimeInSecs – (lngHours * 3600) lngMinutes = lngRemainder \ 60 lngSeconds = lngRemainder – (lngMinutes * 60) FormatTime = Format(lngHours, "0") & ":" & _ Format(lngMinutes, "00") & ":" & _ Format(lngSeconds, "00") End Function Here are some sample results using the function: ?FormatTime(10800) 3:00:00 ?FormatTime(28800) 8:00:00 ?FormatTime(90000) 25:00:00
You’ll probably also want to have a function that will convert from time to time in seconds:
Function TimeInSeconds(TimeIn As Date) As Long Dim lngDays As Long Dim lngHours As Long Dim lngMinutes As Long Dim lngSeconds As Long lngDays = Abs(Int(TimeIn)) * 86400 lngHours = Hour(TimeIn) * 3600 lngMinutes = Minute(TimeIn) * 60 lngSeconds = Second(TimeIn) TimeInSeconds = lngDays + lngHours + _ lngMinutes + lngSeconds End Function
Just to prove what I said previously about the times being in seconds:
?TimeInSeconds(#3:00:00#) 10800 ?TimeInSeconds(#6:00:00#) 21600 ?TimeInSeconds(#8:00:00#) 28800
Now you can use the two functions in combination to calculate durations:
?FormatTime(TimeInSeconds(#3:00:00#) + _ TimeInSeconds(#8:00:00#) + _ TimeInSeconds(#6:00:00#) + _ TimeInSeconds(#8:00:00#)) 25:00:00
In your tables you should be storing durations in seconds in a Long Integer field, rather than in a Date/Time field. A SQL statement that would find total time would be:
SELECT FormatTime(Sum([DurationTM])) AS TotalTime FROM Task;
If, despite what I’ve written here, you’re determined to stay with the date/time type, the alternative is to write a function to reformat the value you got from your sum:
Function ReformatTime(TimeSum As Date) As String Dim lngDays as long Dim lngHours as long Dim lngMinutes as long Dim lngSeconds as long lngDays = Int(TimeSum) lngHours = Hour(TimeSum) + 24 * lngDays lngMinutes = Minute(TimeSum) lngSeconds = Second(TimeSum) ReformatTime = Format(lngHours, "0") & ":" & _ Format(lngMinutes, "00") & ":" & _ Format(lngSeconds, "00") End Function
You can use that function in conjunction with normal times:
?ReformatTime(#3:00:00# + #8:00:00# + _ #6:00:00# + #8:00:00#) 25:00:00
So now, if your durations are in the table as Date/Time fields, a SQL statement to find total time would be:
SELECT ReformatTime(Sum([DurationTM])) AS TotalTime FROM Task;
Okay, that’s about all I have time for this month <groan>.