converting number string

[bmurch71]

Hi! On a weekly basis, I download a .csv file from a vendor that contains product information. I then import that file into our Access products database. I'm looking for an easy way to convert the number in the UPC column to a 13 digit number - as it is, it's anywhere from 7 to 11 digits. I want to add leading zeros, so that 43559012345 becomes 0043559012345. In addition, I need to remove the last digit from the number - it's a check digit that our database does not recognize as part of the UPC. In the past, the UPC did not contain the check digit and I could add zeros by using an update query and updating the UPC field to (Right$('0000000000000',(13-Len([invoice]![upc])))) & [invoice]![upc]. I cannot figure out how to delete the last digit before I do this step. Any suggestions/help would be greatly appreciated. FWIW, the UPC field must be formatted as text for the comparison reports that I then run using our product tables.

 

[Andrew Fergus]

Hi, in answer to this part of your question :

I cannot figure out how to delete the last digit before I do this step. Any suggestions/help would be greatly appreciated.

Try using this :

Left([Your variable name],Len([Your variable name])-1)

This will give you the number without the last digit. You could either use this in a larger statement within a query (which is what I think you want to do), or you could use it as a stand-alone update query to remove the check digit before you do any further manipulation.

HTH, Andrew.

 

[bmurch71]

Excellent! That did the trick - I just took your expression and inserted it into the one I was using, no worries! Thanks heaps for your help.