Adding times with inconsistent formatting

[user888]

Hello all,



I am making a race results database that is supposed to sum an entrant's two times together to obtain a total combined time.

Let's say that the two times I have are '41.43' (41 seconds and 43 milliseconds, there is no colon) and '1:48.17' (1 minute and 48 seconds and 17 milliseconds), and both are currently of the short text data type in my table. How would I go about obtaining the sum of these two times? I have already tried using queries to convert the strings to seconds with no success.

Thanks!

[June7]

Suggest you fix the data to have placeholder zero, like 0:41.43. An UPDATE sql action can do that.

UPDATE tablename SET fieldname = IIf(InStr([fieldname],":")=0, "0:", "") & [fieldname]

Then an expression in query would be fairly simple.

Otherwise, build a custom function in VBA that can handle the absence or presence of colon and parse the string and calculate accordingly.

[ItsMe]

....I have already tried using queries to convert the strings to seconds with no success.....

Date/Time data types that are intrinsic to Access won't play well with fractions. In other words, built in functions like DateDiff and DateAdd return Long Integer data types. It is strange because the actual value of the DateTime is a double data type. But when you introduce a built in function, the fractions of a second go away. The return is truncated by the Long Integer.

I did not download the example, but this link may provide some solutions to help with the math part. It talks about using API's which leads me to believe there may be some formula examples there. I would suspect the formulas are managing time results from the OS's internal clock.
http://www.devx.com/dbzone/Article/39046

[user888]

Thanks for the advice, I will try to implement these suggestions

[June7]

Interesting info but not sure how much it pertains to the OP's situation since the values are strings that represent a value for elapsed time.

1:48.17 = 108.17 seconds calculated with Val(x)*60 + Mid(x,InStr(x,":")+1)

41.43 = 41.43 seconds

Total 149.6 seconds