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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
Back
Top