Access Function to strip leading zeros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a function that would identify and strip leading zeros from a text
string? The number of leading zeros will vary.
 
Try using the CInt function. This will convert your string to an integer
which will strip leading zeroes.

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

Good luck,
Larry
 
Is there a function that would identify and strip leading zeros from a text
string? The number of leading zeros will vary.
Click to expand...

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
 
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?
 
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?
Click to expand...

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
 
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!)



John Vinson said:
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?
Click to expand...

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
Click to expand...
 
Back
Top