Calculating Time

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

Guest

Access2000 Db I have a StartTime and a StopTime users fill in. The time spent is then calculated. I'm having trouble tightening up the entries to prevent errors, such as these 4: forgetting to put in any time; if start is 9:00am and end is 20 seconds later I'd rather default to 1 minute; reversing the values saying they started at 2:10pm and stopped at 2:00pm and then typing in start of 2:00pm and stopping at 2:10am (instead of pm). What I have in now makes it so that the time spent can't exceed 3 hours, but the way it's written it allows time to be null. Can anyone suggest a validation rule that would be better? Which field should it go on? (in the form there is Start, Stop, CalculatedTime). Also, if they go back and correct a stop or start time it needs to continue to catch it.

Currently on the Stop Field is this: Between [CaseActivityStartTime] And ([CaseActivityStartTime]+#3:00:00 AM#

Thx
 
Lynn,

There are possibly a number of approaches to this. But personally, I
wouldn't try to rely on Validation Rules to cope with the complexities
of your requirements. Instead, I would use a vba procedure on the
BeforeUpdate event of the form to validate the entry. Might look
something like this (caution: not tested!)...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Start + Me.Stop) _
Or (Me.Stop < Me.Start) _
Or DateDiff("n",Me.Start,Me.Stop) > 180 Then
Cancel = True
Me.Start.SetFocus
MsgBox "Invalid date entries, please fix"
ElseIf DateDiff("s",Me.Start,Me.Stop) < 60 Then
Me.Stop = DateAdd("n",1,Me.Start)
End If
End Sub
 
Back
Top