Solved Leading Zeros

[drifter96]

I have a table with a account number field that can have mixed data and I need to add leading zeros to anything shorter than 9 characters:

293613590 => 293613590
56908790 => 056908790
6128540590 => 6128540590
3600OLDC79 => 3600OLDC79
43OLDTO7 => 043OLDTO7

It's easy enough to do it if the entire field was numeric, but I'm not sure how to go about this when letter may be present.

Any help would be greatly appreciate.

 

[NauticalGent]

Have a read:

Control data entry formats with input masks - Microsoft Support

Learn how to add input masks in Access to table fields, queries, and to form and report controls to help people entering data.

support.microsoft.com

 

[drifter96]

Nevermind, I found the solution.

Replace(Format(FieldName,"@@@@@@@@@")," ","0")

 

[theDBguy]

Hi. Welcome to AWF!

You could do it in a query using an expression. For example:

IIf(Len([FieldName])<9, Right("000000000" & [FieldName], 9), [FieldName])

Edit: Oops, too slow... Glad to hear you got it sorted out. Cheers!

 

[Gasman]

I have a table with a account number field that can have mixed data and I need to add leading zeros to anything shorter than 9 characters:

293613590 => 293613590
56908790 => 056908790
6128540590 => 6128540590
3600OLDC79 => 3600OLDC79
43OLDTO7 => 043OLDTO7

It's easy enough to do it if the entire field was numeric, but I'm not sure how to go about this when letter may be present.

Any help would be greatly appreciate.

Just prefix with a string of 0 of length the difference between current length and desired length? if you have to store it that way for some reason?

 

[sxschech]

Here is another option for adding leading zero. I found some years back --looks like I didn't note where this came from when originally copied into my code, sorry.

Public Function addZeros(makeLonger As String, numberDigits) As String
    Dim x
    'Adds 0's Leading 0 Leading zero to the front of the number until it's the correct length
    'Change "0" to another character if you need a different character than 0
    For x = 1 To (numberDigits - Len(makeLonger))
        makeLonger = "0" & makeLonger
    Next x
    addZeros = makeLonger
End Function

? addzeros("43OLDTO7",9)
043OLDTO7

 

[Pat Hartman]

I don't know if the input mask solution stores the leading zeros but they need to be stored in order to ensure the data sorts corre