[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Entering 24-hour time with input mask and full validation in Microsoft Access

Published on
10,016 Points
5 Endorsements
Last Modified:
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching all errors.

To enter time is difficult

Entering time in a form in Microsoft Access can be difficult. There is no time picker like the date picker, and if there was, it would be too slow to operate for many users. Even worse, validation is only rudimentary, meaning that errors are often first caught in an unfriendly manner when the user tries to save the field or record.

One way to help the user is to set an input mask, but that adds other problems for the user if he/she clicks inside the textbox or deletes one or more digits, so many users hate input masks. Also, validation is difficult. For example, if the first hour digit is zero or one, the second hour digit can be any digit, but if the first is two, the second can not be higher than four.

How it should work

To have a textbox that just works for entering time takes several steps:
  • Define an input mask that makes sense to the user
  • Handle positioning of the cursor no matter how the user enters the textbox
  • Enable entering of digits no matter how the user navigates in the textbox
  • Catch all invalid inputs
  • Provide reasonable default values to correct invalid inputs
  • Set a format for the textbox
That may seem like a lot, but the outcome is a highly optimised control demonstrating the true power of an input mask.

It takes a lot to get it right

The first and the last item are quite simple.

The input mask is, as the first digit is optional:


and the format is:


This makes the textbox contain as default:


These can be set when the form loads
Option Explicit

    Dim DefaultTime     As Date

Private Sub Form_Load()
    Const InitialTime   As Date = #6:00:00 AM#
    Me!Logon.ShowDatePicker = False
    Me!Logon.InputMask = "90:00;1;0"
    Me!Logon.Format = "hh:nn"
    SetDefaultTime InitialTime

End Sub

Open in new window

That's surprisingly little, but then comes the code behind for the control.

First, set a default time. This may be dynamic (controlled by other code), but here it is static for simplicity:
Private Sub Logon_Enter()
    With Me!Logon
        If IsNull(.Value) Then
            .Value = DefaultTime
        End If
    End With

End Sub

Open in new window

Second, when clicking in the control, avoid ever having to select the separator (colon):
Private Sub Logon_Click()

    With Me!Logon
        If .SelStart = 2 Then
            ' Move the cursor off the separator (colon)
            ' to the first digit of minutes.
            .SelStart = 3
            .SelLength = 1
        End If
    End With

End Sub

Open in new window

Now comes the fun part -- to try to be smart, helping the user by correcting invalid values while still allowing the normal key entries for navigating inside the control as well as away from the control:
Private Sub Logon_KeyPress(KeyAscii As Integer)
    Dim Text        As String
    Dim Char        As String
    Dim Length      As Integer
    Dim SelStart    As Integer
    With Me!Logon
        Select Case KeyAscii
            Case vbKeyBack, vbKeyTab, Asc(vbLf), vbKeyReturn, vbKeyEscape, vbKeyF16
                ' Allow navigation etc. with
                ' BackSpace, Tab, Ctrl+Enter, Enter, Escape, Ctrl+BackSpace
            Case Is > 0
                Text = .Text
                Length = Len(Text)
                SelStart = .SelStart
                If KeyAscii < vbKey0 Or KeyAscii > vbKey9 Then
                    ' Replace any invalid entry with a zero.
                    KeyAscii = vbKey0
                End If
                Char = Mid(Text, 1 + SelStart, 1)
                If SelStart < Length Then
                    If KeyAscii <= vbKey0 + 2 Then
                        ' Always accept 0, 1, 2.
                        ' Check if the text will represent a valid time.
                        ' If not, restore the overwritten digit.
                        Mid(Text, 1 + SelStart, 1) = Chr(KeyAscii)
                        If Not IsDate(Text) Then
                            KeyAscii = Asc(Char)
                        End If
                    End If
                End If
        End Select
    End With

End Sub

Open in new window

The in-line comments explain the function of the crucial parts of the code.
Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const TimeHourMaximum   As Integer = 24
    Const TimeHourDefault   As Integer = 20
    Const TimeMinuteTenMax  As Integer = 5
    Dim ctl                 As Control
    Dim Text                As String
    Dim SelStart            As Integer
    On Error Resume Next
    Set ctl = Screen.ActiveControl
    Select Case ctl.Name
        Case "Logon"
            Text = ctl.Text
            SelStart = ctl.SelStart
            If Not IsDate(Text) Then
                If Val(Left(Text, 2)) > TimeHourMaximum Then
                    Mid(Text, 1) = CStr(TimeHourDefault)
                ElseIf Len(Text) > 3 Then
                    ' Length of Text is larger than two hour digits and the kolon.
                    Mid(Text, 1 + 3) = CStr(TimeMinuteTenMax)
                End If
            End If
            ctl.Text = Text
            ctl.SelStart = SelStart
            ctl.SelLength = 1
            Response = acDataErrContinue
    End Select

    Set ctl = Nothing

End Sub

Open in new window

Note, that it will always leave some time value in the control.

When done, you may set a new default value:
Private Sub Logon_AfterUpdate()

    With Me!Logon
        If IsNull(.Value) Then
            ' Rem this line out to allow the textbox to be cleared.
            .Value = #12:00:00 AM#
            SetDefaultTime DateAdd("n", 1, .Value)
        End If
    End With
End Sub

Private Sub SetDefaultTime(ThisTime As Date)

    DefaultTime = ThisTime
    Me!Logon.DefaultValue = Format(ThisTime, "\#hh:nn:00 AM/PM\#")

End Sub 

Open in new window

Note the conversion of the time value as a formatted string expression, as DefaultValue is a string.

Check it out

To test it for yourself, download and run the demo application. It is a simple form created in Access 2013  that should work as is for all versions of Access from 2007 to 2016. However, the code origins from Access 2.0, so it should be adoptable with minor changes for any version of Access.
You can also obtain the code from Github: https://github.com/GustavBrock/VBA.TimeEntry.git
LVL 55

Author Comment

by:Gustav Brock
Thanks. Have a nice weekend!

LVL 67

Expert Comment

by:Jim Horn
Nice job walking through code and explaining it, and thanks for attaching examples.  Voting Yes.

Featured Post

Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Join & Write a Comment

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month