asked on

Converting seconds to minute:second

I'm wondering if anyone has any suggestions for truncating the decimals in a calculated field in a query. The field seems to ignore the property setting for zero decimal places. As indicated by my subject I have a field ([avg speed ans]) that displays data in a total number of seconds in which the calculated field ([asa_mins]) converts to minutes (e.g. 120 seconds is calculated to 2 minutes), yet if the number of seconds doesn't come out to a nice, even number of minutes:seconds it displays several decimal places (e.g. 122 seconds gets converted to 3:20.754379272). How could I truncate this? Is it some form of usage of MID$ or something similar? Below is the calculated field. Thanks in advance for any suggestions!

ASA_Mins: Int([AvG Speed Ans]/60) & ":" & ([AvG Speed Ans]-(60*Int([AvG Speed Ans]/60)))

Kevin Cross

Think you have it. I copied your query as is to query in Access and runs and returns 2:2. This is pretty much correct, just have to pad the left of the seconds with 0 if not 2 characters in length. Using String(2 - LEN( ([AvG Speed Ans]-(60*Int([AvG Speed Ans]/60)))), "0"). Hopefully, my copy and paste didn't screw up brackets there. :)

If still not working for you, you can try wrapping with Round(value, 0) to round to zero decimal places.

ASKER CERTIFIED SOLUTION

GRayL

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Mark Wills

Use the ROUND function, but you are right, it looks like it should do the right thing - especially if int([avg speed ans]/60) does return a 2... maybe best to use dattime functions e.g. using an arbitrary date...

format((dateadd("s",122,#01/01/2008 00:00:00#) - #01/01/2008 00:00:00# ),"hh:mm:ss")

should give you 00:02:02

GRayL

Thanks, glad to help. You realize my solution is limited to any 59 minutes 59 seconds. If you have any number larger than that, then make the format string "hh:nn:ss". this will give you leading zeroes for anything under one hour.