how to remove Part Numbers with varying 2 to 6 zeros in front

[William Poh Ben]

Thanks to GVaught who helped me with the solution by using Find
/Replace.. and Set action to: Start of Field... that removed all the
leading 6 zero's from the part numbers leaving intact just the value
after the zero's. However, there's still a little problem here I can't
solve.

Does anybody know how to remove all the leading zeros when the Part
Numbers in the column have varying zeros before the values (eg. 2 to 6
zeros in stead of only 6 zeros thru-out before the values) ?

Thanks in advance to anyone for the help.

An example pf Part Numbers below:
12220-89210
12220-89211E
12221-00110
00866751158666
00004255781123
00000125667233
00000027791001

 

[Ken Snell]

Assuming that your PartNumber field is text, you might run an Update Query
that uses the following expression as the "Update To:" value for the field:

IIf(IsNumeric([PartNumber])=True, CStr(Val([PartNumber])), [PartNumber])

Watch for line-wrapping. This is one line.

The above assumes that none of your part numbers has a value such as this:
000123456-1234
0000000123E

 

[Gary Miller]

You could create a Public Function to do this and then call it in an Update
Query or from code. This will loop through the PartNumber and strip the left
characters out until it runs into one that isn't a zero

Public Function StripZeros(strNum as String)

Do Until Left(strNum,1) <> "0"
strNum = Right(strNum,Len(strNum)-1)
Loop

StripZeros = strNum

End Function

Gary Miller

 

[Gary Miller]

Also my suggestion could be modified with an IF statement that used InStr()
to examine for a "-" or anything else that you needed to exempt from
processing.

Gary Miller