Removing leading zeroes

[QueryStumped]

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]

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

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

in the criteria

Then use Mid function to return the rest of it

MyNewNumber: Mid([YourField],2)

 

[bastanu]

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

 

[QueryStumped]

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

 

[QueryStumped]

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

 

[arnelgp]

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

 

[QueryStumped]

Thank you for the help. Really appreciate it.

 

[QueryStumped]

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