Text Field with leading Zero's

[Tupacmoche]

I have a input form that takes an id which is 10 characters long. This id has leading zero. Most users will copy this id from another system which does not have the leading zero's in it, so it will look like this: 1593582 but, I want the leading zero's to be added automatically when they paste in that number. The name of the field is id and I tried something like this:

Right("0000000000"+ID,10)

If the id is already present it will simple return the Id that has all ten characters but if not when it is concatenated with the zero in my expression it will still return 10 characters. But the above expression is not working. Can anyone see what is wrong?:banghead:

 

[isladogs]

Try adapting something like this:

String(10 - Len(strID), "0") & strID

For example, to test it:

Sub TEST()

Dim strID As String
strID = "123456"

Debug.Print String(10 - Len(strID), "0") & strID

End Sub[CODE]

For example
123456 => 0000123456
3421     => 0000003421

HTH

 

[pbaldy]

Format() should also work:

Format(ID, "0000000000")

 

[Tupacmoche]

Hi Ridders,

Sorry for the delayed replay, thanks for your suggested solution. I'm using it to format any input into a text box. So, for example if a user paste a number like '1570570' which is seven character long it will end up as follows: '0001570570' with ten character the leading zero's being added automatically. So, should it be coded like this:

Private Sub TxtInput_BeforeUpdate(Cancel As Integer)

Dim StrFix As String
StrFix = Me.TxtInput.Value ' Let's say the user past in '1570570'
String(10 - Len(StrFix), "0")& StrFix

End Sub

 

[isladogs]

Hi

No - use the AfterUpdate event
Make sure the txtInput control is a BOUND field
Also make sure the field it is bound to is a TEXT field of length 10
This will prevent users typing or pasting something more than 10 characters

Then use this:

Private Sub txtInput_AfterUpdate()
    Dim StrFix As String
    StrFix = Me.txtInput.Value ' Let's say the user past in '1570570'
    Me.txtInput = String(10 - Len(StrFix), "0") & StrFix
End Sub

See attached for a very simple db showing this in practice

Alternatively try pbaldy's approach