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é

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

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é

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...

if the numbers with leading zeroes are numeric only then

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

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é