How can I return minutes:seconds in a date/time field?

[Paul]

All my attempts at having a date/time field return minutes:seconds don't
work. I have "nn:ss" as a format, but cannot build an input mask to go along
with it. 00:00 always wants to be hours:minutes.

Thanks

 

[Allen Browne]

Yes, the entry will be interpreted as hours and minutes.

What is this for? Would it be appropriate to use 2 Number fields to hold teh
minutes and seconds respectively? Or would it be better to store the value
in just one Seconds field?

In general, the date/time field is not a good choice for storing durations.

 

[Paul]

Hi Alan,

The entry is for elapsed time and will always be less than an hour.

 

[Allen Browne]

Elapsed time is best stored in a Number field (Long Integer) for the number
or seconds.

For data entry, you can interface it with 2 unbound text boxes, so the user
can enter minutes and seconds. In this example, the field is named
"Seconds", and the unbound text boxes are named "txtMinutes" and
"txtSeconds".

Private Sub txtMinutes_AfterUpdate()
If Not (IsNull(Me.txtMinutes]) And IsNull(Me.txtSeconds)) Then
Me.Seconds = 60 * Nz(Me.txtMinutes, 0) + Nz(Me.txtSeconds, 0)
End If
End Sub

Private Sub txtSeconds_AfterUpdate()
Call txtMinutes_AfterUpdate
End Sub

Private Sub Form_Current()
If IsNull(Me!Seconds) Then
Me.txtMinutes = Null
Me.txtSeconds = Null
Else
Me.txtMinutes = Me!Seconds \ 60
Me.txtSeconds = Me!Seconds Mod 60
End If
End Sub

For display purposes (e.g. on a report), you can show the data in a text box
with this ControlSource:
=[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00")

More info:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[Douglas J Steele]

Date fields aren't really intended to be used for Elapsed times: they're
intended for "point in time" timestamps. This is because under the covers,
they're really an 8 byte floating point number, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day.

What's generally recommended for elapsed times is to store them as long
integers representing the lowest level of granularity required (apparently
seconds in your case), and write your own custom functions to translate
between nn:ss and total seconds.

 

[Bob Miller]

Date/time is not for entering elapsed time which is what it looks like
what you want.
Try using a Text field with this as the input mask:
00:00;0;_