Solved Update Query to Add Missing 0 at the front of a telephone number? (1 Viewer)

Number11

Member
Local time
Today, 14:24
Joined
Jan 29, 2020
Messages
607
Is it possible to have an update query to add a missing 0 to a telephone number
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:24
Joined
Jan 23, 2006
Messages
15,362
I'm sure it's possible, but how do you determine that 0 is missing?

This may apply

Code:
' ----------------------------------------------------------------
' 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
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 14:24
Joined
Sep 21, 2011
Messages
14,044
Can't you just use Format()?
Code:
tt=1792555147
? tt
 1792555147 
? format(tt,"00000000000")
01792555147
 

Number11

Member
Local time
Today, 14:24
Joined
Jan 29, 2020
Messages
607
I'm sure it's possible, but how do you determine that 0 is missing?

This may apply

Code:
' ----------------------------------------------------------------
' 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

Super Moderator
Staff member
Local time
Today, 10:24
Joined
Jan 23, 2006
Messages
15,362
Happy to help.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:24
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom