The problem is because Access doesn't have a Time datatype: the Date type
supports time, but only in the sense of a timestamp. What's happening under
the covers is that Dates are stored as 8 byte floating point numbers, where
the integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.
The recommended approach to working with durations in Access is to use a
different data type. If you need to go down to minutes, use a Long Integer
to store minutes, and write your own function to format Total Minutes to
hh:nn.
If you don't want to do this, you can write your own function to translate
the integer part. Something like the following untested aircode should work:
Function FormatTimeHHMM(ValueIn As Date) As String
Dim intDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
intDays = Day(ValueIn)
intHours = Hour(ValueIn)
intMinutes = Minute(ValueIn)
FormatTimeHHMM = (intDays * 24 + intHours) & _
":" & Format(intMinutes, "00")
End Function
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
frank said:
i am having trouble displaying times in reports... it occurs when the
hours go over 24. I have the format of the field set as 'short time' so 1
hour 55 mins shows as 01:55, but 35 hours 20 mins is showing as 11:20
because ACCESS treats 01:55 as 01/01/1901 01:55 and 35:20 as 01/02/1901
11:20. In EXCEL you can enter a custom format of [HH]:mm to overcome this
but it does not work in ACCESS.
