Excell Check Digit Formula

[tnelson]

Ok guys..Any help would be great.. What i'm trying to accomplish her
is. Creating a function and/or formula in Excell or access that wil
calculate the check digit value of a 20 digit number. I have see
several sites that have VB code on how to do..however I do not know ho
to incorporate vb code into a Ms Access db and make it do my requeste
task...

ie... http://www.makebarcode.com/info/appnote/app_003.html
http://www.excelbanter.com/q-t_19172-Mod-10-Check-Digit.html
http://www.freevbcode.com/ShowCode.Asp?ID=1035

ie... I have a field "barcodewithnocheckdigit" and an example valu
for that field is "8260212616630008000" The check digit sum for tha
figure is 9. I have 40,000 records. Is it possible to have thi
code/query calculate and update the check digit value field?

The instructions on calculating a check digit value are here :

http://tinyurl.com/joqy


So I guess my question should be "how do I use this code in m
database" Please keep in mind that I have no programming knowledg
whatsoever...therefore I need step by step information pretty much..

Thank in advance for your help

 

[Ian]

Excel can not handle a 20 digit number. If you enter 11111111111111111111 it
returns 11111111111111100000 when formatted as a number with 0 decimal
places. To get round this you would need to split the number into 2 parts.
The easiest way would be 1st10 in one cell and last 10 in another (Assume A1
& B1). You can then use the following formula (eg in C1)

=10-(RIGHT(((LEFT(A1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(B1,1,1)+MID(B1,3,1)+MID(B1,5,1)+MID(B1,7,1)+MID(B1,9,1))*3)+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+RIGHT(A1,1)+MID(B1,2,1)+MID(B1,4,1)+MID(B1,6,1)+MID(B1,8,1)+RIGHT(B1,1),1))

This formula should all be on 1 line.

BTW, your 20 digit number only had 19 digits. Adding an extra 0 at the end
gave the required result.