Solved

Use Input Mask to autofill zeros in Client ID field

Posted on 2003-12-02
3
600 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
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

680 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