• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Use Input Mask to autofill zeros in Client ID field

Hi Guys,
I am wondering if I can use an input mask to achive the following:

I have a client ID field in a Customers table in my database.

The Client ID is of the following form(all ID's have to be 10 chars long for uniformity):

03 is the year and then a literal forward slash separates year part from unique number part which must be 7 digits filled left with zeroes.

So with the above example in mind I would like to set up an input mask so all the user has to tap on the keyboard is 0 3 / 1 2 3
and what is displayed in field and saved is 03/0000123
i.e. the system automatically left fills the 4 zeroes before 123 to make sure the Clients ID total length is 10 characters  

Is this possible using Input Mask


B Cunney

Barry Cunney
Barry Cunney
  • 2
1 Solution
Instead of an Imput Mask use VB Code to accomplish this on the afterupdate event of the cell where they enter 03/123.

Private Sub txMyText_AfterUpdate()
Dim DelimPos
Dim strText As String

    strText = txMyText.Value
    'Verify Delimiter in the string
    DelimPos = GetFirstOccurance(strText, "/")
    If DelimPos = 0 Then
        MsgBox ("Invalid Value")
        Exit Sub
    'Fill Before Delimiter
        If DelimPos < 3 Then
             strText = PadLeft(Left(strText, DelimPos), "0", 3 - DelimPos) & Right(strText, Len(strText) - DelimPos)
             DelimPos = 3
        End If
    'Fill After Delimiter to Value
        If Len(strText) < 10 Then
            strText = Left(strText, 3) & PadLeft(Mid(strText, DelimPos + 1, Len(strText) - 3), "0", 10 - Len(strText))
        End If
    'set new value
    txMyText.Value = strText
    End If
End Sub
Function GetFirstOccurance(str As String, char As String) As Integer
'Returns first occuance of char within str
Dim pos
Dim Found As Boolean
    pos = 1
    Do While pos <= Len(str) And Not Found
        If Mid(str, pos, 1) = char Then
            Found = True
            pos = pos + 1
        End If
    If Found Then
        GetFirstOccurance = pos
        GetFirstOccurance = 0
    End If
End Function
Function PadLeft(str As String, char As String, buffer As Integer) As String
'Pad left of str with char buffer times
    For padding = 1 To buffer
        str = char & str
    Next padding
    PadLeft = str
End Function

     Did not hear back and just wanted to know if the example worked for you

Enjoy the Holidays

Barry CunneyAuthor Commented:
Hi DarkMor,
Sorry for the delay in getting back to you but I was busy with other things.

Thank you very much for the solution above - it worked perfectly.
That's the best help I 've seen anyone giving on EE.

Hope to touch bases with you again

Many Thanks

B Cunney
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now