how to remove zero from the begining of a string in access by query ?

Question

_{Sunday, March 18, 2012 7:06 AM | 1 vote}

hi

how to remove zero from the begining of a string in access by query ?

for example:

00001234  ->  1234

000abc     ->  abc

thanks in advance

All replies (16)

_{Sunday, March 18, 2012 7:38 AM ✅Answered | 8 votes}

Ok, you may try this in your Query:

NewField: Replace(LTrim(Replace([YourTextField],'0',' ')),' ','0')

Hope this helps,

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

_{Sunday, March 18, 2012 7:13 AM | 2 votes}

Try this function, copy it in a seperate Module, save it as modStripLeadingZero, and then you can use in your query like:

RemoveLeadingZeros([YourTextField])

Public Function RemoveLeadingZeros( _
ByVal strValue) As String

' source:
' http://www.pcreview.co.uk/forums/removing-leading-zeroes-text-field-t2248049.html
' Test if there is at least 1 leading zero
If Left(strValue, 1) = "0" Then

Do While True 'fContinue 'And (intPosition <= intLen)

If Mid(strValue, 1, 1) = "0" Then
strValue = Replace(strValue, "0", "", 1, 1, vbTextCompare)
Else
' reached the first non-zero string
Exit Do
End If

Loop

Else
' Does not have a leading zero
End If

RemoveLeadingZeros = strValue

End Function

Hope this helps,

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

_{Sunday, March 18, 2012 7:26 AM}

thank for the help,

but is there any built-in fonction for this ?  

i dont want to use VB code

_{Sunday, March 18, 2012 9:08 AM}

thanks !!!!

_{Sunday, March 18, 2012 12:27 PM | 1 vote}

Wouldn't this be shorter? 

Replace([txtField],"0","")

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

_{Wednesday, August 20, 2014 3:34 AM}

That would remove all zeroes, not leading zeroes.

_{Thursday, January 18, 2018 7:01 PM}

This worked perfect,  thank you for posting 

_{Wednesday, April 25, 2018 3:58 PM}

this worked awesome thank you.. is there a way to put a letter in front of the text after removing the 0's ??

_{Thursday, September 20, 2018 7:07 PM}

Thank you for this, I've been using it for years now ;)

_{Friday, September 28, 2018 2:01 PM}

I have the same need and this example is removing all Zeros, is there a way to remove just the Leading Zeros?

_{Saturday, December 8, 2018 6:22 PM}

It is definitely shorter, but then it would remove all zeroes from the string rather than just the leading zeroes.

_{Friday, March 8, 2019 10:23 PM}

This happened because I removed the spaces. You need the spaces to stay in so that the spaces are replaced with 0. The LTrim removes the leading spaces

Thanks again for this.

_{Saturday, March 9, 2019 3:21 PM}

You missed the optimal solution:

TrimmedNumber = CStr(Val([YourField]))

and it will not remove inline zeroes.

Gustav Brock

_{Saturday, March 9, 2019 8:21 PM}

TrimmedNumber = CStr(Val([YourField]))

Indeed, a nice short one-liner.

But it is not usable for both given examples:

    for example:

    00001234  ->  1234

    000abc     ->  abc

Imb.

_{Sunday, March 10, 2019 10:30 AM}

You are right. Don't know why I missed that. Thanks.

Gustav Brock

_{Tuesday, May 19, 2020 6:52 PM}

NICE!!!!!