FORMAT to show minutes and seconds?

[Maury Markowitz]

Maybe this just doesn't work, but:

startTime = 12:30:08 PM
stopTime = 12:34:21 PM

Format((stopTime - startTime), "h:m:s") = "0:4:13"

Format((stopTime - startTime), "m:s") = "12:13"

Huh? Is there any way to display a minutes/seconds format? The task
I'm timing typically takes about 4 to 6 minutes, I don't want hours to
show up.

Maury

 

[Douglas J. Steele]

Since both month and minute start with m, one of them had to get changed.
Use n instead:

Format((stopTime - startTime), "n:s")

(Of course, that's not really how times are intended to be used in Access.)

 

[Chegu Tom]

So how are times intended to be used in Access?

You left a teaser there

 

[Maury Markowitz]

Thanks! "n" does not appear in the list in Format Function help...

Maury

 

[Douglas J. Steele]

The Date data type is intended to be used as a timestamp (a complete date
and time). Under the covers, they're eight byte floating points number,
where the integer portion represents the date as the number of days relative
to 30 December, 1899, and the decimal portion represents the time as a
fraction of a day. You can easily see this by applying a complete format to
a time field:

?Format(#08:00:00 AM#, "yyyy-mm-dd hh:nn:ss")
1899-12-30 08:00:00

When dealing strictly with times, you'll run into problems when they cross
midnight. For instance, to the average person, 11:30:00 PM to 1:30:00 AM is
2 hours, but check what Access thinks:

?Format(#1:30:00 AM# - #11:30:00 PM#, "hh:nn:ss")
22:00:00

That's actually misleading, since #1:30:00 AM# - #11:30:00 PM# results
in -0.916666666666667

Doing the opposite calculation, you'll see what I mean:

?Format(#11:30:00 PM# - #1:30:00 AM#, "hh:nn:ss")
22:00:00
?#11:30:00 PM# - #1:30:00 AM#
0.916666666666667

To get it correct, you need to take the date part into account:

?Format(#2008-04-22 1:30:00 AM# - #2008-04-21 11:30:00 PM#, "hh:nn:ss")
02:00:00

 

[Chegu Tom]

Doug

Thanks for that answer. A very nice explanation

Tom