Solved Update Query to Add Missing 0 at the front of a telephone number?

[Number11]

Is it possible to have an update query to add a missing 0 to a telephone number

 

[jdraw]

I'm sure it's possible, but how do you determine that 0 is missing?

This may apply

' ----------------------------------------------------------------
' Procedure Name: addZeros
' Purpose: Routine to add leading 0's to a string
' Procedure Kind: Function
' Procedure Access: Public
' Parameter makeLonger (String): the original string
' Parameter numberDigits (): length of required output FINAL STRING)
' Return Type: String
' Author: Jack from internet
' Date: 09-Feb-22
' ----------------------------------------------------------------
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

Sample usage:

?addzeros("345",8)
00000345

 

[Gasman]

Can't you just use Format()?

tt=1792555147
? tt
 1792555147 
? format(tt,"00000000000")
01792555147

 

[Number11]

I'm sure it's possible, but how do you determine that 0 is missing?

This may apply

' ----------------------------------------------------------------
' Procedure Name: addZeros
' Purpose: Routine to add leading 0's to a string
' Procedure Kind: Function
' Procedure Access: Public
' Parameter makeLonger (String): the original string
' Parameter numberDigits (): length of required output FINAL STRING)
' Return Type: String
' Author: Jack from internet
' Date: 09-Feb-22
' ----------------------------------------------------------------
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

Sample usage:

?addzeros("345",8)
00000345

Did the job thanks

 

[jdraw]

Happy to help.

 

[Pat Hartman]

Did you change the Zip field to text. It may only contain numbers in the US but it is a "code" NOT a number you do arithmetic with so like phone numbers and SSN and license numbers, should always be stored as a string.

Living in New England where our Zips start with leading zeros, I run into this mistake all the time. Many times the problem is caused by Excel being the intermediary. If you use Excel, you have to coerce Excel into storing the zip as a string.