Access Function to strip leading zeros

[Guest]

Is there a function that would identify and strip leading zeros from a text
string? The number of leading zeros will vary.

 

[Guest]

Try using the CInt function. This will convert your string to an integer
which will strip leading zeroes.

Good luck,
Larry

 

[Guest]

Try using the CInt function. This will convert your string to an integer
which will remove leading zeroes.

Good luck,
Larry

 

[John Vinson]

Is there a function that would identify and strip leading zeros from a text
string? The number of leading zeros will vary.

Depends a bit on the size of the field. CInt(), as Larry suggests,
will work for numbers up to 65535; CLng() will work up to two billion
odd. If the "numbers" are longer than nine digits post back, I could
throw together a quick VBA function.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks for the reply - I wasn't specific enough. C(Lng) would be plenty long
enough for me, but the problem is the field has a mix of both numeric and
alpha characters as text, for example:

EmplID
VNGL04
001352

I need to remove the leading zeros from the numeric string only. VNGL04
would stay as is, but 001352 would be converted to 1352. Any ideas on that
one?

 

[John Vinson]

Thanks for the reply - I wasn't specific enough. C(Lng) would be plenty long
enough for me, but the problem is the field has a mix of both numeric and
alpha characters as text, for example:

EmplID
VNGL04
001352

I need to remove the leading zeros from the numeric string only. VNGL04
would stay as is, but 001352 would be converted to 1352. Any ideas on that
one?

How about 003ABC? Should that become 3ABC?

If not: update to

IIF(IsNumeric([field], CStr(CLng([field]), [field])

If so:

Public Function StripZero(strIn As String) As String
Dim iPos As Integer
iPos = 1
Do While Mid(strIn, iPos, 1) <> "0"
iPos = iPos + 1
Loop
StripZero = Mid(strIn, iPos)
End Function

Update the field to StripZero([field]).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Douglas J. Steele]

I believe there's an error in John's code.

The intent is to increment iPos until it's past any 0's at the beginning of
the string, so it should be
Do While Mid(strIn, iPos, 1) = "0"
not
Do While Mid(strIn, iPos, 1) <> "0"

In other words, the function should be:

Public Function StripZero(strIn As String) As String
Dim iPos As Integer
iPos = 1
Do While Mid(strIn, iPos, 1) = "0"
iPos = iPos + 1
Loop
StripZero = Mid(strIn, iPos)
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply - I wasn't specific enough. C(Lng) would be plenty long
enough for me, but the problem is the field has a mix of both numeric and
alpha characters as text, for example:

EmplID
VNGL04
001352

I need to remove the leading zeros from the numeric string only. VNGL04
would stay as is, but 001352 would be converted to 1352. Any ideas on that
one?

How about 003ABC? Should that become 3ABC?

If not: update to

IIF(IsNumeric([field], CStr(CLng([field]), [field])

If so:

Public Function StripZero(strIn As String) As String
Dim iPos As Integer
iPos = 1
Do While Mid(strIn, iPos, 1) <> "0"
iPos = iPos + 1
Loop
StripZero = Mid(strIn, iPos)
End Function

Update the field to StripZero([field]).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

I believe there's an error in John's code.

OOPS! Thanks Douglas. I started with a Do Until but switched in
midstream, and didn't test the code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps