Remove leading zeros

[BeArkens]

Our product range includes letter codes and numeric codes.

We upload these product codes (1 column) as a text field in access. This gives us egg. "000000000111" for code "111".

1 uploaded table (short) is an excel table that shows the code as "111".
Can you help me to convert "000000000111" into "111"?

Much appreciated.
Regards
Bé

http://www.access-programmers.co.uk/forums/forumdisplay.php?s=&daysprune=&f=75

 

[raskew]

Hi -

Welcome to the forum.

You need to lookup the Val() function in the Help File. Here's an example from the debug (immediate) window:

x = "000000000111"
? val(x)
 111

HTH - Bob

 

[BeArkens]

Thanks Bob.
This helps for the codes with the leading zeros. The other codes however get changed as well (incorrectly).
This is what happed:
1) 000000000000001582 >1582 >ok
2) 01-06-0023-9 >1 >nok
3) NL230637612 > 0 > nok

How can I make 1) happen, but 2) and 3) keep the original code?

Thanks
Kind Regards
Bé

 

[namliam]

Is this number always 12 characters??
If so... You can add Like "############" to the field to only convert those fields which contain actually 12 numbers...

 

[gemma-the-husky]

if the numbers with leading zeroes are numeric only then

iif(isnumeric(myvalue),val(myvalue),myvalue) would probably fix them

 

[BeArkens]

Hi all,
Thanks for your replies. Some guidelines will surely help me completing this database.
FYI the function that worked well for my issue was:

Public Function StripZeros(pstr As String) As String
Dim n As Integer
n = 1
Do While Mid(pstr, n, 1) = "0"
n = n + 1
Loop
n = IIf(n > 1, n, 1)
StripZeros = Mid(pstr, n)

End Function
Thanks again and have nice evening.
Rgds
Bé