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>.