Solved

Use Input Mask to autofill zeros in Client ID field

Posted on 2003-12-02
3
614 Views
Last Modified: 2011-10-03
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

0
Comment
Question by:Barry Cunney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
DarkMor earned 100 total points
ID: 9884449
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
 
LVL 5

Expert Comment

by:DarkMor
ID: 9983072
BCunney,
     Did not hear back and just wanted to know if the example worked for you

Enjoy the Holidays

D
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 10034875
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question