G
Guest
Is there an Access function (or combination of functions) that can remove
leading zeroes from a text string?
leading zeroes from a text string?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
fredg
Is there an Access function (or combination of functions) that can remove
leading zeroes from a text string?
Does the text string contain numbers after the leading zeros?
=Val("0005670")
returns 5670 as a number.
=Val("000ABC")
returns 0.
R
RobFMS
If its a text string you have to search:
Public Sub RunMe()
Dim strValue As String
Dim strNewValue As String
strValue = "000A1B2C300"
strNewValue = RemoveLeadingZeros(strValue)
MsgBox Prompt:="The string without leading zeros is: " & strNewValue
End Sub
Public Function RemoveLeadingZeros( _
ByVal strValue) As String
' 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
HTH
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
Public Sub RunMe()
Dim strValue As String
Dim strNewValue As String
strValue = "000A1B2C300"
strNewValue = RemoveLeadingZeros(strValue)
MsgBox Prompt:="The string without leading zeros is: " & strNewValue
End Sub
Public Function RemoveLeadingZeros( _
ByVal strValue) As String
' 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
HTH
Rob Mastrostefano
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
FMS Advanced Systems Group
http://www.fmsasg.com/
J
John Vinson
Is there an Access function (or combination of functions) that can remove
leading zeroes from a text string?
If it's numeric, use Val([fieldname]), or CStr(Val([fieldname])) to
get back to a string.
If it's text, or mixed numeric and text, after the zeros, you'll need
a little VBA function. Here's a recursive one off the top of my head,
test it first:
Public Function StripZeros(strIn As String) As String
If Left(strIn, 1) = "0" And Len(strIn) > 1 Then
StripZeros = StripZeros(Mid(strIn, 2))
Else
StripZeros = strIn
End If
End Sub
John W. Vinson[MVP]
R
RobFMS
John .. you did recursion ... eeewww!!! LOL!
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
--
FMS Professional Solutions Group
http://www.fmsinc.com/consulting
Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com
John Vinson said:Is there an Access function (or combination of functions) that can remove
leading zeroes from a text string?
If it's numeric, use Val([fieldname]), or CStr(Val([fieldname])) to
get back to a string.
If it's text, or mixed numeric and text, after the zeros, you'll need
a little VBA function. Here's a recursive one off the top of my head,
test it first:
Public Function StripZeros(strIn As String) As String
If Left(strIn, 1) = "0" And Len(strIn) > 1 Then
StripZeros = StripZeros(Mid(strIn, 2))
Else
StripZeros = strIn
End If
End Sub
John W. Vinson[MVP]
J
John Vinson
John .. you did recursion ... eeewww!!! LOL!
A programmer who lived in Racine
Said "I can overload any machine -
My secret's aversion
To loops and recursion,
Just acres of inline routine!"
It's all right to write recursive code... just don't do it in public,
and wash your hands afterwards! <g>
John W. Vinson[MVP]
T
Tom Lake
Is there an Access function (or combination of functions) that can remove
Why not use LTrim?
Tom Lake
If it's text, or mixed numeric and text, after the zeros, you'll need
a little VBA function. Here's a recursive one off the top of my head,
test it first:
Why not use LTrim?
Tom Lake
T
Tom Lake
Tom Lake said:Why not use LTrim?
Tom Lake
OK, I know why. I thought of spaces while reading Zeros. Never mind!
LTrim(Str(Val(n)))
should work, though.
Tom L
J
John Vinson
OK, I know why. I thought of spaces while reading Zeros. Never mind!
LTrim(Str(Val(n)))
should work, though.
redundant since Val() already does the trimming... and it will fail
for input data such as "0000A32KW". The OP hasn't posted back with
more details so we may never know!
John W. Vinson[MVP]
T
Tom Lake
LTrim(Str(Val(n)))
Not redundant since the Str function puts a leading space for positive
numbers.
Tom L
redundant since Val() already does the trimming... and it will fail
for input data such as "0000A32KW". The OP hasn't posted back with
more details so we may never know!
Not redundant since the Str function puts a leading space for positive
numbers.
Tom L
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.