Calculated Time Field Formatting

[Zippyfrog]

Right now I am working on a query that figures out average time for how long it takes an athlete to run a mile.

The query works fine, all the calculations are done correctly, however, the formatting is incorrect.

If the average time to run a mile is 7 minutes and 5 seconds, it should be displayed as "7:05", however, the way it is set up now it displays "7:5". Here is the how I set up the query:

There are two fields: FinalTimeInMinutes and Seconds

I then use a lot of expressions:

Expr1 = [FinalTimeInMinutes * 60] + Seconds = TotalTimeInSeconds

Expr2 = [TotalTimeInSeconds]/[Distance] = TimeInSecondsPerMile

Expr3 = Fix([TimeInSecondsPerMile]/60)

Expr4 = [TimeInSecondsPerMile] Mod 60

AverageTime = Expr3 & ":" & Expr4


What do I add to my code so that if Expr4 is between 0 and 9, it is displayed at 00, 01, 02... 09?

 

[Zippyfrog]

I made some progress...

I changed the property in the query so that the format in Expr4 has to be "00", so now the "1" shows as "01" and the others, such as 32 are not affected.

However, for my average time expression, it seems to ignore the fact that it is now displaying 01, and still thinks it should be 1.

(Example, 6:1 is showing instead of 6:01 even though Expr = 6 and Expr 4 = 01)

Any ideas on how to fix this new issue? Or is there another way around it?

 

[Zippyfrog]

Thank you!!! It works great now!