Forcing a user to enter time in 24 hour mode.

[cwhite23]

Good morning.

I am working on an idea for a form for a project and need some advice. The form will have several fields on it, but I'm only interested in two of them. They will be called [Start_Time] and [End_Time]. I know how to get the difference between the two, but what I need is for each field to only allow a time to be entered if it is between 7:00 am and 4:00 PM, without having to rely on the user to enter the time correctly. Ideally, what I would like is for the field to look at the time, decide if it falls out of the range, and change it accordingly. For example, if a user enters 2:00 (instead of 14:00 for 24 hour time), the system would automatically add 12 hours to compensate. I need to do this on both fields, but if you can show me how to do it on one, I can adapt it for the other.

Any help is ,as always, greatly appreciated.

Bear in mind that this is still in the conceptual stage, so no screen shot is available yet.

 

[Gammachaser]

Try this

Private Sub txtStartTime_AfterUpdate()
If txtStartTime = "" Then Exit Sub
Select Case txtStartTime
    Case #7:00:00 AM# To #2:00:00 PM# ' This Does nothing since it is OK
    Case #12:00:00 AM# To #2:00:00 AM# ' This adds 12 hours
        txtStartTime = Format(DateAdd("h", 12, txtStartTime), "hh:mm")
    Case Else ' This handles invalid times
        MsgBox "Time Out of Range"
        txtStartTime = ""
End Select
End Sub

You would have to either set the format of the control to Short Time or do some validation in the txtStartTime_BeforeUpdate event procedure to make sure you get a valid time. Invalid times will always get the 'Time Out of Range' message.

 

[cwhite23]

Beautiful!

This is exactly what I needed. I'm still learning VBA, and would never have guessed about the 'Case' command.

Thanks, Gammachaser! 5 stars!

 

[TheAceMan1]

How are ya cwhite23 . . .

Why not simply use the [blue]Short Time[/blue] format?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]