Prev: Open mode
Next: Forms Field List
From: John W. Vinson on 18 Feb 2010 17:53
On Thu, 18 Feb 2010 13:49:01 -0800, Karl <Karl(a)discussions.microsoft.com>
wrote:

>Thanks, I was afraid it would be something like that.
>
>Having to enter hours, minutes and seconds in separate fields isn't optimal.
> The whole idea behind this database is to move away from the unwieldy Excel
>spreadsheet that we've been doing this work on. Excel does have a problem
>with recording and averaging times... I can hear the users' whining already
>(even if Access does many other things better than Excel for our purposes).
>
>Some of these values may be over 24 hours, so I can't use Ken's solution.
>
>I entered some test values and was able to convert hours and minutes into
>seconds, add them all up and get the average; howeer, when I use John's
>formula I get a three digit value for the seconds (hours and minutes were
>fine). It is also off by about 11 seconds from the average of the same
>values in Excel.

Sorry... typo (or brainfade) on my part; the formula should be

[duration] \ 3600 & ":" & Format([duration] \ 60 MOD 60, "00:") &
Format([duration] MOD 60, "00")

with a MOD rather than an integer divide for the seconds.

You can make data entry a bit easier by having a Form with four textboxes:
three unbound, for hours, minutes and seconds, and the fourth bound to
Duration. In the afterupdate event of each of the unbound textboxes include
code like

Private Sub txtHrs_AfterUpdate()
Me!txtDuration = 3600*NZ(Me!txtHrs) + 60*NZ(Me!txtMin) + NZ(Me!txtSec)
End Sub

This can be made more sophisticated if you want - as written it will store
86400 in the Duration field if the user just types 24 in txtHrs and leaves the
other two blank. You might or might not want that!

You can also put code in the form's Current event to do the reverse:

Private Sub Form_Current()
If Not IsNull(Me!txtDuration) Then
Me!txtHrs = Me!txtDuration \ 3600
Me!txtMin = (Me!txtDuration \ 60) MOD 60
Me!txtSec = Me!txtDuration MOD 60
End If
End Sub
--

John W. Vinson [MVP]
First  |  Prev  | 
Pages: 1 2
Prev: Open mode
Next: Forms Field List