Validate Time entry before update

[Guest]

Hello,
Seems like I am full of questions today. I have a text box for the user to
enter in a time, such as min:sec. Access is requiring the user to enter two
digits, then a colon then 2 digits.
How can I sparse out the entry, validate that the entry is in the correct
format, if not correct it and then update the bound field?
User enters 3:15 (for 3 min 15 seconds), before update change it to 03:15?

Any help you can give me is greatly appreciated, I've been searching the
MSDN Access posts for hours now and can't seem to find a solution.

 

[Allen Browne]

David, if you just enter 3:15 into a date/time field, Access will interpret
it as 3 hours 15 minutes, not 3 minute 15 seconds.

The leading zero is not needed (unless you have added a useless Input Mask.)

It would be possible to use the AfterUpdate event of the control to test
whether 2 colons were entered, and if there is only one to recalculate the
value as if it were minutes and seconds, rather than hours and minutes. The
limitation of this approach is that it stops working at 24 minutes, because
that is not a valid number of hours to enter in the first place.

If you want to experiment with that approach, it would be something like
this:

Private Sub MyTime_AfterUpdate()
Dim lngPosColon1 As Long 'Position of first colon.
Dim lngPosColon2 As Long 'Position of second colon.
Dim iHour As Integer 'Hour value to change to minutes.
Dim iMinute As Integer 'Minute value to change to seconds.

With Me.MyTime
lngPosColon1 = InStr(.Text, ":")
If lngPosColon1 > 0 Then
lngPosColon2 = InStr(lngPosColon1 + 1, .Text, ":")
If lngPosColon2 = 0 Then
iHour = DateDiff("h", #12:00:00 AM#, .Value)
iMinute = DatePart("n", .Value)
.Value = DateAdd("s", 60 * iHour + iMinute, #12:00:00 AM#)
End If
End If
End With
End Sub