Problem with UPC #'s

[kstump]

I have a very weird situation . . . I have two different sytems which contain a UPC #. System 1 has the UPC set up correctly as an 11 digit number.

System 2 has the UPC set up as an 13 digit number. I need to take that number and convert it to match the 11 digit in the System 1 so that I can link them.

So far I am using LEFT(UPC,11) which works, BUT this number will only give me a return of 10 digits if there is a leading 0; therefore, I'm ending up with some numbers 11 digits and some 10.

Is there a code I can write which would count the number of digits and if there are only 10 it would add a leading 0?

 

[RuralGuy]

How about: LEFT( Format(UPC, "00000000000"),11)

 

[modest]

If it's a string datatype, it will keep the leading 0; Unless you're expecting to do calculations with your UPC #s (which you should not because they are IDs) then you shouldn't be storing them as a number, unless of course they are auto-numbered by the application.

 

[kstump]

It Worked

Thank you very much, it worked perfectly

 

[RuralGuy]

You're very welcome and thanks for posting back with your success.