Remove zero from first letter

[Ranjit kurian]

Is it possible to remove zeros, in the beggining of the letter if it contain

example:

00CA258
000VDB
0000CA963
0US456

 

[Jerry Whittle]

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 = IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

 

[Klatuu]

This expression will remove all numeric characters from the beginning of a
string:
=replace(x,Cstr(Val(x)),"")
Where x is the string with the leading numeric characters.

 

[Ranjit kurian]

Hi Jerry,

i want to remove ALL the leading zeros...

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 = IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Is it possible to remove zeros, in the beggining of the letter if it contain

example:

00CA258
000VDB
0000CA963
0US456

 

[John Spencer]

Possibly not too accurate. Especially since the user only wanted to remove
leading zeroes.

x = "001234abc"
Replace(x,Val(x),"")
00abc

x = "0012abc12"
?Replace(x,Val(x),"")

Jerry Whittle's solution also suffers as it only removes the leading zero (not
all leading zeroes).

If this was a one time event then running the following query multiple times
would fix things.

UPDATE [YourTable]
SET YourField = Mid(YourField,2)
WHERE YourField Like "0?*"

Otherwise, I suspect the best solution would be a VBA function to strip off
the leading zeroes.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Bob Barrows [MVP]]

A VBA function would be needed. Like this:

Function StripLeadingChars(byval original as string, _
leadchar as string) as string
dim newstring as string: newstring = original
Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,2)
loop
StripLeadingChars=newstring
end function

Select StripLeadingChars(fieldname) as Stripped
from tablename

Hi Jerry,

i want to remove ALL the leading zeros...

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 =
IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

Is it possible to remove zeros, in the beggining of the letter if
it contain

example:

00CA258
000VDB
0000CA963
0US456

 

[Bob Barrows [MVP]]

Oops! The query should read:

Select StripLeadingChars(fieldname,"0") as Stripped

Also, the function could be revised to strip multicharacter strings as
well:

Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,len(leadchar)+1)
loop

A VBA function would be needed. Like this:

Function StripLeadingChars(byval original as string, _
leadchar as string) as string
dim newstring as string: newstring = original
Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,2)
loop
StripLeadingChars=newstring
end function

Select StripLeadingChars(fieldname) as Stripped
from tablename

Hi Jerry,

i want to remove ALL the leading zeros...

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 =
IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

:

Is it possible to remove zeros, in the beggining of the letter if
it contain

example:

00CA258
000VDB
0000CA963
0US456

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Ranjit kurian]

Hi Bob,

thanks for the code, but its not working, the systems is hanging in loop
part, i mean there is debug in loop code, the loop is not stopping..

After copying your below function to module1, i selected the query as shown
below

Select StripLeadingChars(Coid,"0") as Stripped
From [Account];

Oops! The query should read:

Select StripLeadingChars(fieldname,"0") as Stripped

Also, the function could be revised to strip multicharacter strings as
well:

Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,len(leadchar)+1)
loop

A VBA function would be needed. Like this:

Function StripLeadingChars(byval original as string, _
leadchar as string) as string
dim newstring as string: newstring = original
Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,2)
loop
StripLeadingChars=newstring
end function

Select StripLeadingChars(fieldname) as Stripped
from tablename

Hi Jerry,

i want to remove ALL the leading zeros...

:

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 =
IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

:

Is it possible to remove zeros, in the beggining of the letter if
it contain

example:

00CA258
000VDB
0000CA963
0US456

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Have you set a breakpoint to allow you to step through the code to see what
is happening? I admit to posting it without debugging it. Which version did
you use? I just noticed a flaw in the revision I made to allow
multicharacter strings to be stripped. It should be:

Do until Left(newstring ,len(leadchar)) <> leadchar
not
Do until Left(newstring ,1) <> leadchar

I will go and debug it to see what is happening. I suggest you do the same.
If you haven't posted the answer when I come back, I will post the debugged
version I come up with

Hi Bob,

thanks for the code, but its not working, the systems is hanging in
loop part, i mean there is debug in loop code, the loop is not
stopping..

After copying your below function to module1, i selected the query as
shown below

Select StripLeadingChars(Coid,"0") as Stripped
From [Account];

Oops! The query should read:

Select StripLeadingChars(fieldname,"0") as Stripped

Also, the function could be revised to strip multicharacter strings
as well:

Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,len(leadchar)+1)
loop

A VBA function would be needed. Like this:

Function StripLeadingChars(byval original as string, _
leadchar as string) as string
dim newstring as string: newstring = original
Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,2)
loop
StripLeadingChars=newstring
end function

Select StripLeadingChars(fieldname) as Stripped
from tablename

Ranjit kurian wrote:
Hi Jerry,

i want to remove ALL the leading zeros...

:

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 =
IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

:

Is it possible to remove zeros, in the beggining of the letter if
it contain

example:

00CA258
000VDB
0000CA963
0US456

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

Ah, I see. This:
original =mid(newstring ,len(leadchar)+1)
should be this:
newstring =mid(newstring ,len(leadchar)+1)

Duh!

Have you set a breakpoint to allow you to step through the code to
see what is happening? I admit to posting it without debugging it.
Which version did you use? I just noticed a flaw in the revision I
made to allow multicharacter strings to be stripped. It should be:

Do until Left(newstring ,len(leadchar)) <> leadchar
not
Do until Left(newstring ,1) <> leadchar

I will go and debug it to see what is happening. I suggest you do the
same. If you haven't posted the answer when I come back, I will post
the debugged version I come up with

Hi Bob,

thanks for the code, but its not working, the systems is hanging in
loop part, i mean there is debug in loop code, the loop is not
stopping..

After copying your below function to module1, i selected the query as
shown below

Select StripLeadingChars(Coid,"0") as Stripped
From [Account];

Oops! The query should read:

Select StripLeadingChars(fieldname,"0") as Stripped

Also, the function could be revised to strip multicharacter strings
as well:

Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,len(leadchar)+1)
loop


Bob Barrows [MVP] wrote:
A VBA function would be needed. Like this:

Function StripLeadingChars(byval original as string, _
leadchar as string) as string
dim newstring as string: newstring = original
Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,2)
loop
StripLeadingChars=newstring
end function

Select StripLeadingChars(fieldname) as Stripped
from tablename

Ranjit kurian wrote:
Hi Jerry,

i want to remove ALL the leading zeros...

:

Do you want to remove ALL the leading zeros or just the first
one? If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 =
IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

:

Is it possible to remove zeros, in the beggining of the letter
if it contain

example:

00CA258
000VDB
0000CA963
0US456

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will
get a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Ranjit kurian]

Hi Bob

Thanks a lot, its working fine.......................

Oops! The query should read:

Select StripLeadingChars(fieldname,"0") as Stripped

Also, the function could be revised to strip multicharacter strings as
well:

Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,len(leadchar)+1)
loop

A VBA function would be needed. Like this:

Function StripLeadingChars(byval original as string, _
leadchar as string) as string
dim newstring as string: newstring = original
Do until Left(newstring ,1) <> leadchar
original =mid(newstring ,2)
loop
StripLeadingChars=newstring
end function

Select StripLeadingChars(fieldname) as Stripped
from tablename

Hi Jerry,

i want to remove ALL the leading zeros...

:

Do you want to remove ALL the leading zeros or just the first one?

If just the first 0 put in the the right field and table names,

UPDATE tblZeros
SET tblZeros.Field1 =
IIf(Left([Field1],1)=0,Mid([Field1],2),[Field1]);

Make a backup first.

:

Is it possible to remove zeros, in the beggining of the letter if
it contain

example:

00CA258
000VDB
0000CA963
0US456

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[John Spencer]

Looks ok, but it won't handle nulls. I usually pay the penalty on that
and use a variant as the argument when I know that can be passed in.
Otherwise I have to remember to check if what I am sending to the VBA
function is null.

Then I just use

If Len(Trim(pStr & "")) = 0 Then
StringFix = pStr
ELSE
'Optionally Force conversion of value to a string
'so it can handle dates and numbers as well as strings
'No real reason it should, but why not.
strHold = pStr & ""
Do While Left(strHold, 1) = pdelim
strHold = Mid(strHold, 2)
Loop

StringFix = strHold
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John Spencer -

What do you think?

Bob

Hi -

The following function will strip specified leading characters, regardless
of the number.
*******************************************************************************
Public Function StringFix(pstr As String, pdelim As String) As String
Dim strHold As String

strHold = pstr

Do While Left(strHold, 1) = pdelim
strHold = Mid(strHold, 2)
Loop

StringFix = strHold

End Function
*******************************************************************************
To test, from the debug/immediate window:

? stringfix("00ABC340", "0")
ABC340

? stringfix("00000ABC340", "0")
ABC340
*******************************************************************************

The challenge becomes, if you want to remove all leading "0"s and "A"s,
but don't know in what sequence they occur. If you know for certain that
there's n "0"s, followed by i "A"s, it's pretty simple, but if you are not
sure
whether "0"s occur before "A"s, it becomes more interesting.

I'm working on it and will post back if I can come up with a solution.

HTH - Bob

Possibly not too accurate. Especially since the user only wanted to remove
leading zeroes.

[quoted text clipped - 28 lines]

=replace(x,Cstr(Val(x)),"")
Where x is the string with the leading numeric characters.