Removing leading zeroes (1 Viewer)

Q

QueryStumped

Registered User.

Local time

Today, 03:35

Joined

Mar 22, 2017

Messages

60

• Jan 24, 2018

• #1

Hello. Having issues with a table with Phone Number field, which is programmed to include 0 at the beginning. I am needing to remove the leading zero through a update query, leaving 10 characters instead of 11. This field is a short text field in the table it does have a input mask of !\(999")"000\-000;;_. Any help is very appreciated.

 

Minty

AWF VIP

Local time

Today, 08:35

Joined

Jul 26, 2013

Messages

10,366

• Jan 24, 2018

• #2

You can get any number that has a leading zero by using

Code:Copy to clipboard

Left([YourField],1) = "0"

in the criteria

Then use Mid function to return the rest of it

Code:Copy to clipboard

MyNewNumber: Mid([YourField],2)

 

bastanu

AWF VIP

Local time

Today, 00:35

Joined

Apr 13, 2010

Messages

1,402

• Jan 24, 2018

• #3

here is an expression that uses Minty's suggestion in one field (create your update query and use the expression in the "UpdateTo" row):

IIf(Left([PhoneNumber],1)="0",Mid([PhoneNumber],2),[PhoneNumber])

Cheers,
Vlad

 

Q

QueryStumped

Registered User.

Local time

Today, 03:35

Joined

Mar 22, 2017

Messages

60

• Jan 24, 2018

• #4

Really appreciate your help. Will use once I return to the office. Thank you Thank you Thank you

 

Q

QueryStumped

Registered User.

Local time

Today, 03:35

Joined

Mar 22, 2017

Messages

60

• Jan 24, 2018

• #5

Thank you for your help. I will apply it once I get back to the office. Appreciate very much

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 15:35

Joined

May 7, 2009

Messages

19,232

• Jan 25, 2018

• #6

Fot double test, test the length if indeed 11.

Update table set field=right(field,10) where left(field,1)="0" and len(field)=11

 

Q

QueryStumped

Registered User.

Local time

Today, 03:35

Joined

Mar 22, 2017

Messages

60

• Feb 12, 2018

• #7

Thank you for the help. Really appreciate it.

 

Q

QueryStumped

Registered User.

Local time

Today, 03:35

Joined

Mar 22, 2017

Messages

60

• Feb 12, 2018

• #8

Thank you to all that replied. This website has tremendously have helped and saved me through dome sticky situations. I really appreciate it.