Turn 2 Digits into 7 digits.

[ConfusedNAccess]

I have a field that contains a TEXT data which could be a number with 2 digits up to 7 digits.

I need all records to show as 7 digits.

so if the record shows 52- I need to see:
0000052.

I've tried to use an imput mask. It's not working. Any ideas?

 

[rjoubert]

Can you change the field to a Number(Integer)? If so, you can set the format of the field to "0000000". Each zero in that format tells it to display a digit (if one exists) or zero.

 

[ConfusedNAccess]

no because it has been set as a text field in another table. In order for me to join the 2 similar fields, they both need to be formated as text

 

[rjoubert]

Or, they could both be Number fields. Why can't you change the other table as well?

 

[ConfusedNAccess]

it is linked to a program that I can't modify. If i modify it, it may cause conflict for other users

 

[rjoubert]

I can't really see how to do this in the table design, but perhaps a function to pad the appropriate number of zeros to each record would do the trick?

 

[rjoubert]

Something like this...

Public Function ZeroPadMe(StringToPad As String, TargetLength As Integer) As String

    Dim strTemp As String
    Dim intLoop As Integer
    Dim i As Integer

    strTemp = StringToPad
    intLoop = TargetLength - (Len(strTemp) + 1)
    
    For i = 0 To intLoop
        strTemp = "0" & strTemp
    Next
    
    ZeroPadMe = strTemp
    
End Function

 

[cmmrfrds]

If the field you are joining to in the other table is a number then you can convert it for the join.

Something like

yournumberfield = val(nz(othertablefield,0))
OR
yournumberfield = Clng(nz(othertablefield,0))