Results 1 to 10 of 10
Thread: Check Digit logic in Access

111506, 15:54 #1Registered User
 Join Date
 Jan 2006
 Posts
 28
Unanswered: Check Digit logic in Access
I am working on an Access Database that has a field I need to run check digit logic on. I am not sure how to set this up to indicate whether the number entered into the field is valid or not. This is the logic used to define the validity of a 10 digit number. I want to make sure that the 10th digit of a number is equal to the result of the following calculation using the first 9 digits of the same number.
Can someone help me with this please.
The following steps are involved in this calculation:
Step 1: Double the value of alternate digits beginning with the first
righthand digit (low order).
Step 2: Add the individual digits comprising the products obtained in
step 1 to each of the unaffected digits in the original number.
Step 3: Subtract the total obtained in step 2 from the next higher
number ending in 0 [this in the equivalent of calculating the
"tens complement" of the low order digit (unit digit) of the total].
If the total obtained in step 2 is a number ending in zero
(30, 40 etc.), the check digit is 0.
Example:
Account number without check digit: 4992 73 9871
4 9 9 2 7 3 9 8 7 1
x2 x2 x2 x2 x2

18 4 6 16 2
4 + 1 + 8 + 9 + 4 + 7 + 6 + 9 + 1 + 6 + 7 + 2 = 64
70  64 = 6
Account number with check digit 4992 73 9871 6

111706, 13:58 #2(Making Your Life Easy)
 Join Date
 Feb 2004
 Location
 New Zealand
 Posts
 1,469
Provided Answers: 10this show point you down the right path
Code:Function My_Code(CheckThis) Dim firstbit As Double Dim Nextup As Long Dim ANSWER As Long 'the first bit firstbit = 0 ANSWER = 0 newline = "" Check = Replace(CheckThis, " ", "") 'remove spaces For AA = 2 To Len(Check) Step 2 factor = Mid(Check, AA, 1) firstbit = (Val(factor) * 2) newline = newline & Mid(Check, AA  1, 1) + Trim(Str(firstbit)) Next For AA = 1 To Len(newline) ANSWER = ANSWER + Val(Mid(newline, AA, 1)) Next NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0") THISANSWER = Val(NEXTTEN)  Val(ANSWER) My_Code = CheckThis & " " & THISANSWER End Function
and you test have only tested on 4992 73 9871 which works
aaa = My_Code("4992 73 9871")
aaa has the value 4992 73 9871 6Last edited by myle; 111706 at 14:08.
hope this help
StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VBNET based on my own environment started 2007
SQL2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON

111706, 15:53 #3Registered User
 Join Date
 Jan 2006
 Posts
 28
One more question
That makes sense. I have one more question. If I want this logic to check information entered into a text box on a form, how do I link it to the text box? Also, I want it to produce an error message if the result does not match the 10th digit.

111706, 21:58 #4(Making Your Life Easy)
 Join Date
 Feb 2004
 Location
 New Zealand
 Posts
 1,469
Provided Answers: 10what is the a error one
in a textbox
=My_Code([anothertextboxinthisform])
then I change the code
don't forget to paste code into a modulehope this help
StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VBNET based on my own environment started 2007
SQL2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON

111906, 19:29 #5(Making Your Life Easy)
 Join Date
 Feb 2004
 Location
 New Zealand
 Posts
 1,469
Provided Answers: 10Try this
Code:Function My_Code(CheckThis) Dim firstbit As Double Dim Nextup As Long Dim ANSWER As Long 'the first bit firstbit = 0 ANSWER = 0 newline = "" Check = Replace(CheckThis, " ", "") 'remove spaces '***************************************** CodeCheck = Right(Check, 1) 'get the last digit Check = Mid(Check, 1, Len(Check)  1) 'take last digit off '***************************************** For AA = 2 To Len(Check) Step 2 factor = Mid(Check, AA, 1) firstbit = (Val(factor) * 2) newline = newline & Mid(Check, AA  1, 1) + Trim(Str(firstbit)) Next For AA = 1 To Len(newline) ANSWER = ANSWER + Val(Mid(newline, AA, 1)) Next NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0") THISANSWER = Val(NEXTTEN)  Val(ANSWER) '************************************************* If Trim(CodeCheck) <> Trim(THISANSWER) Then My_Code = "Check Sum Error" Else My_Code = CheckThis End If '************************************************* End Function
hope this help
StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VBNET based on my own environment started 2007
SQL2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON

112106, 14:32 #6Registered User
 Join Date
 Jan 2006
 Posts
 28
I appreciate all of the help you have given to me. I found out this morning that the logic I was given was wrong. Below is what the logic should have been.
Step 1: Double the value of alternate digits beginning with the Second
righthand digit (low order).
Step 2: Add the individual digits comprising the products obtained in
step 1 to each of the unaffected digits in the original number.
Step 3: Add 24 to total from step 2
Step 4: Subtract the total obtained in step 3 from the next higher
number ending in 0 [this in the equivalent of calculating the
"tens complement" of the low order digit (unit digit) of the total].
If the total obtained in step 2 is a number ending in zero
(30, 40 etc.), the check digit is 0.
Step 5: Verify that the resulting number equals the 10th digit in the original
number.
Account Number: 4992 73 9878
4 9 9 2 7 3 9 8 7 8
x2 x2 x2 x2 x2
8 9 18 2 14 3 18 8 14
8+ 9+ 1+8+2+1+4+3+1+8+8+1+4 = 58
58 + 24 = 82 90  82 = 8 X=8
If X <> 10th digit in account number, then Error Message: "Invalid Number"
If X = 10th digit in account number, then no message.
Thanks,

112706, 14:41 #7Registered User
 Join Date
 Jan 2006
 Posts
 28
If you could document what these different steps are for, I can make the changes to make it fit what I need now. I am having a little difficulty understanding exactly what each thing is telling me.
Thanks,
Originally Posted by myle

112706, 18:19 #8(Making Your Life Easy)
 Join Date
 Feb 2004
 Location
 New Zealand
 Posts
 1,469
Provided Answers: 10old way
4 9 9 2 7 3 9 8 7 1
x2 x2 x2 x2 x2

18 4 6 16 2
4 + 1 + 8 + 9 + 4 + 7 + 6 + 9 + 1 + 6 + 7 + 2 = 64
code was
want to loop the len of the code looking @ the Sec number
here aa = 2 4 6 8 .....
For AA = 2 To Len(Check) Step 2
factor = Mid(Check, AA, 1)
firstbit = (Val(factor) * 2)
newline = newline & Mid(Check, AA  1, 1) + Trim(Str(firstbit))
Next
new way
4 9 9 2 7 3 9 8 7 8
x2 x2 x2 x2 x2
8 9 18 2 14 3 18 8 14
8+ 9+ 1+8+2+1+4+3+1+8+8+1+4 = 58
'know you say look @ the first number loop the sec number
aa = 1 3 5 7 9 ....
For AA = 1 To Len(Check) Step 2
factor = Mid(Check, AA, 1) 'read the number from the string
firstbit = (Val(factor) * 2) ' muply it by 2
newline = newline & Mid(Check, AA + 1, 1) + Trim(Str(firstbit))
' newline build the line back up
Next
hope this helpshope this help
StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VBNET based on my own environment started 2007
SQL2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON

112806, 10:14 #9Registered User
 Join Date
 Jan 2006
 Posts
 28
I figured that out yesterday afternoon. Thanks for getting back with me though. Below is the logic that I wrote in yesterday. It works with some of the numbers I have tried, but with a couple of them, it comes back one or two digits off.
I needed to add 24 to the final number that I got. I added it onto the end of the newline. Should I have added it differently, or should this work?
Thanks,
Function My_Code(CheckThis)
Dim firstbit As Double
Dim Nextup As Long
Dim ANSWER As Long
'the first bit
firstbit = 0
ANSWER = 0
newline = ""
Check = Replace(CheckThis, " ", "") 'remove spaces
'*****************************************
CodeCheck = Right(Check, 1) 'get the last digit
Check = Mid(Check, 1, 9) 'take last digit off
'*****************************************
For AA = 1 To Len(Check) Step 2
factor = Mid(Check, AA, 1)
firstbit = (Val(factor) * 2)
newline = newline & Mid(Check, AA + 1, 1) + Trim(Str(firstbit)) + 24
Next
For AA = 1 To Len(newline)
ANSWER = ANSWER + Val(Mid(newline, AA, 1))
Next
NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")
THISANSWER = Val(NEXTTEN)  Val(ANSWER)
'My_Code = CheckThis & " " & THISANSWER
'*************************************************
If Trim(CodeCheck) <> Trim(THISANSWER) Then
My_Code = "Not Valid"
Else
My_Code = "Valid"
End If
'*************************************************
End Function

112806, 12:12 #10Registered User
 Join Date
 Jan 2006
 Posts
 28
I figured out what I needed to do to make this work. Below is the new code I am using, and it works. This is for anyone who might be looking for something like this.
Function My_Code(CheckThis)
Dim firstbit As Double
Dim Nextup As Long
Dim ANSWER As Long
'the first bit
firstbit = 0
ANSWER = 0
newline = ""
Check = Replace(CheckThis, " ", "") 'remove spaces
'*****************************************
CodeCheck = Right(Check, 1) 'get the last digit
Check = Mid(Check, 1, 9) 'take last digit off
'*****************************************
For AA = 1 To Len(Check) Step 2
factor = Mid(Check, AA, 1)
firstbit = (Val(factor) * 2)
newline = newline & Mid(Check, AA + 1, 1) + Trim(Str(firstbit))
Next
For AA = 1 To Len(newline)
ANSWER = ANSWER + Val(Mid(newline, AA, 1))
Next
ANSWER = ANSWER + 24
NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")
IF Val(Right(ANSWER,1)) = 0 Then
THISANSWER = 0
Else
THISANSWER = Val(NEXTTEN)  Val(ANSWER)
End If
'*************************************************
If Trim(CodeCheck) <> Trim(THISANSWER) Then
My_Code = "Not Valid"
Else
My_Code = "Valid"
End If
'*************************************************
End Function