Time Field 100ths

[meridklt]

I'm using Access 2000 and trying to make a data type Time Field of 12:01:02.23

Unlike Excel which lets you format a cell using Custom hh:mm:ss.00 I don't see any custom option to create what I want.

Is there some way to solve this, or perhaps I have missed it in Help.

 

[Rickster57]

time

Access stores the time field as 8-byte floating point number. So, setting it to hundredths (atleast to my knowledge) won't work. You could store it as text and then use math equations to interpret it I suppose.

 

[RuralGuy]

If it were vital to your project, I suspect you could create your own data type. As it is, this article should help explain why you are having difficulty.
http://support.microsoft.com/kb/130514/en-us

 

[The_Doc_Man]

1. A Date/Time field will support that level of precision. (See discussion later for how much precision you can get.)

2. The Windows System Clock might possibly support that level of precision. (See discussion below.)

3. The format routines don't understand that level of precision. Period.

You will have to "roll your own" fractional seconds time by doing the following:

a. Convert the Date/Time field to a string of hours, minutes, and seconds. Keep this string in a convenient place. You will generate your fractions separately and tack them onto this string.

b. Now extract the time fraction as a separate number. Multiply it by 86400 (the number of seconds in a day.) Extract the fraction again, discarding the integer number of seconds. What is left is your fractions of a second. You can convert this to a fraction using a Format function, then extract the leading zero that Format would normally give you and keep the next two digits (and the decimal point). Tack this onto your date string.

Be aware that a time of 1 day plus 1 second looks like this in DOUBLE format: 1.00001157407407 - which isn't an even number.

You are going to be dealing with a LOT of roundoff errors in doing this. If I remember the math correctly, you get 55 bits in a DOUBLE mantissa. The current Windows date is something like 39094 (give or take a few days). That takes up not less than 16 mantissa bits. You have 86400 seconds in a day. That takes up another 17 mantissa bits. Totalling not less than 33 bits of the mantissa occupied to store a time precisely to 1 second. Which gives you 22 bits unaccounted for in the 55-bit mantissa. Clearly, you have enough bits for two more digits of the fraction. All you need is another 7 bits to keep hundredths of a second. So that takes about 40 bits out of the 55.

However, the time of day in hours, minutes, and seconds is based on sexagesimal numbers (base 60) that have factors of 2, 3, 4, and 5. The 3 and 5 as fractional factors lead to irrational numbers when expressing them in binary. So you get that abominable repeating decimal sequence I showed you earlier. I still think you have enough bits, but remember that you are dealing with truncated / rounded representations of irrational numbers, which can lead to rapid degradation of the number quality.

Then there is the question of how precise is the Windows System Clock? How often does it update? No matter how many bits are stored in the fraction, you can never get more bits than the hardware clock supports. It used to be that Windows clocks were on the 60 Hz power frequency, which means one tick of the system clock added 0.000000192901234567901 to the Date/Time as a DOUBLE. (Doubt me? Compute (1/60)/86400, which is the day fraction of 1/60th of a second.) So the numbers you are dealing with are inherently ugly. By the way, that is EXACTLY why you would want to not have to do your own date manipulation in general. So many ugly fractions.

 

[meridklt]

Thanks to you all for responding to my question. It's been enlightening and it looks like I'm going to have to stay with Excel to complete my task.

 

[w1dge]

Try this function that I use when converting time in milliseconds to real time.

Time is diplayed as a string that you could manipulate to give the level of precision you require. It may be a little agricultural, but it works for me.

Function MillisecondsToTime(ByVal lngMilliseconds As Long) As String

Dim strHours As String
Dim lngHours As Long
Dim strMinutes As String
Dim lngMinutes As Long
Dim dblSeconds As Double
Dim strMinFmt As String
Dim strSecFmt As String

lngHours = lngMilliseconds \ 3600000
lngMilliseconds = lngMilliseconds Mod 3600000
lngMinutes = lngMilliseconds \ 60000
lngMilliseconds = lngMilliseconds Mod 60000
dblSeconds = lngMilliseconds / 1000#

If lngHours > 0 Then
strHours = lngHours & ":"
strMinFmt = "00"
Else
' Added extra 0 into formatting string regardless, so should be able to delete ELSE
strMinFmt = "00"
End If

If lngMinutes > 0 Then
strMinutes = Format(lngMinutes, strMinFmt) & ":"
strSecFmt = "00:000"
Else
' Added extra 0 into formatting string before :, so should be able to delete ELSE
strSecFmt = "00:000"
End If

MillisecondsToTime = _
strHours & strMinutes & Format(dblSeconds, strSecFmt)

End Function


Hope this helps

W1dge