Calculating time differences for a swimmer in Access 2019

Question

I'm having trouble calculating time differences for a swimmer in Microsoft Access 2019. How can I do this properly?

Answer 1

Use the DateDiff functions. See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datediff-function

Also works for times.

Answer 2

It depends on the precision to which the times are recorded.  The date/time data type in Access has a precision of one second, so if you are only storing the times at that precision you can do so as a Date/Time data type and use simple arithmetic to get the difference, formatting the result in minutes:seconds.  You can see how this would work in the immediate window: 

Time1 = #00:27:46#

Time2 = #00:29:05#

 

? Format(Time2-Time1,"nn:ss")

01:19

 

The Format function returns a string expression, so, if you need to undertake further arithmetic on the value this should be done on the unformatted result of the subtraction, applying the format only as the final stage.

 

If you are recording the times to a precision of a fraction of a second, however, you have a couple  of options:

 

1.  Store the value as a string expression.  In this case to undertake arithmetic on the values you'd have to parse it into (a) a string expression which can be interpreted as the value to a precision of one second, and (b) a string expression which can be interpreted as a decimal fraction of a second.  The string expressions would then be converted to a date/time data type and a double precision number data type respectively.  To compute the difference you'd then firstly do so to a precision of one second with simple subtraction as above.  Then subtract the fractions of a second.  However, if the fractional value of Time2 is less than the fractional value of Time 1, this would return a negative number, so you'd have to concatenate (NB not add) 1+ the value of the result to the result of the subtraction to a precision of one second, and reduce that by one second.

 

2.  Rather than storing the times as a single value, store them in two columns, the first as a date/time value, and the second as a decimal fractional number representing the fraction of a second of the time.  Computing the difference would then be the same as in 1 above.

 

It's very likely that somebody has written a function which does the above, so a little googling may well turn up something you could use.

Answer 3

Since my last post here I have added a small file to my OneDrive Public Databases folder, which illustrates how to compute the difference between two times to a precision of one millisecond. You’ll find the file as PreciseTimes.zip in my public databases folder at:

https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg

The times are stored in two columns, one of normal date/time data type, the other of double precision number data type to store the fraction of a second values.