Fractional seconds

[Jeroen]

Hi,

I am creating a database for a swimming association and they need to enter
100ths of a second.
The problem is, Access does not seem to support fractional seconds. I can, of
course, enter the time as a text field, but I need Access to calculate the total
time.
Maybe it is possible to use a seperate field for the 100ths and add a second to
the time field every 100 fractions using a query. Or maybe I can accomplish this
task with a little Visual Basic code?

I hope someone can help me out on this or send me in the right direction.

Thanks in advance,
Jeroen

 

[Rob Parker]

I suspect that, even though Access's Date/Time datatype allows it to store
time values to a precision greater than you will need, you will be unable to
recover/display it in a format including decimal seconds.

Since you seem to be wanting to work in decimal seconds, I suggest that you
use a numeric datatype in which you simply store times in seconds. Set it
to either single or double (or decimal, if you wish - I prefer not to use
that sub-datatype), depending on the largest time you'll ever want to store
(see Access Help for details of number ranges). Using multiple textboxes
with text datatype(s) is only going to be pleasant if you're a real
masochist!!!

If you need to enter times in minute:second.decsecond format, you could use
unbound textboxes for each portion, multiply the minute entry by 60, and add
the second.decseconds value; store the result in your table via code in the
form's events. You could use either the AfterUpdate events of each textbox,
or the BeforeUpdate event of the form, depending on exactly what you've got
set-up and happening, with a statement such as:

Me!Myseconds = (Me!txtMinutes * 60) + Me!txtSeconds

where MySeconds is the name of the field (in a recordset boud to the form)
where you are storing the time, and txtMinutes and txtSeconds are the names
of two unbound textboxes. Some error-trapping to catch incorrect entries
(eg, non-numeric entries) would be advisable ;-)

HTH

Rob

 

[John Spencer]

Access' DateTime field is designed to record a point in time, not a duration
of time.

You need to record a duration in time with an accuracy of 1/100 of a second.

I would use the field type of currency for this since it will accurately
store number with 4 decimal positions. Then I would store the number of
seconds (and partial seconds) in the field.

Or an alternative, use a number field - type long to store the elapsed time
(duration) in the number of hundredths of a second. So 1 second = 100, 1
minute = 6000, etc.

You will have to write VBA to translate the display of these durations into
a minutes, seconds, and partial seconds view. And you would probably need
another routine to allow data entry to be minutes, seconds and fractions of
seconds and then turn that into an long integer value for storage.

6135 would become 1:01.35

The VBA to convert this for display might look something like

Public Function fMakeSeconds(lngSeconds) As String
Dim strOut As String

If IsNull(lngSeconds) Then
fMakeSeconds = "00:00.00"
Else
strOut = "." & Format(lngSeconds Mod 100, "00")
strOut = ":" & Format(Int(lngSeconds / 100) Mod 60, "00") & strOut
strOut = Int(lngSeconds / 6000) & strOut
fMakeSeconds = strOut
End If
End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

After Reading Rob Parker's post, I realized that you might be better off
using three unbound controls on a form for data entry and using VBA
functions to set the values in the three controls and then to combine and
store the values.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..