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/0000123

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


Cheers


________
B Cunney

LVL 17
Barry CunneyAsked:
Who is Participating?

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

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DarkMorCommented:
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
    Else
    '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
        Else
            pos = pos + 1
        End If
    Loop
   
    If Found Then
        GetFirstOccurance = pos
    Else
        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


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarkMorCommented:
BCunney,
     Did not hear back and just wanted to know if the example worked for you

Enjoy the Holidays

D
0
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.