# Thread: Check Digit logic in Access

1. Registered 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
right-hand 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

Join Date
Feb 2004
Location
New Zealand
Posts
1,469
this show point you down the right path

Code:
```Function My_Code(CheckThis)
Dim firstbit As Double
Dim Nextup As Long
'the first bit
firstbit = 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)
Next

NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")

My_Code = CheckThis & " " & THISANSWER

End Function```
paste to module

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 6
Last edited by myle; 11-17-06 at 14:08.

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

Join Date
Feb 2004
Location
New Zealand
Posts
1,469
what is the a error one

in a textbox

=My_Code([anothertextboxinthisform])

then I change the code

don't forget to paste code into a module

Join Date
Feb 2004
Location
New Zealand
Posts
1,469
Try this

Code:
```Function My_Code(CheckThis)
Dim firstbit As Double
Dim Nextup As Long
'the first bit
firstbit = 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)
Next

NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")

'*************************************************
My_Code = "Check Sum Error"
Else
My_Code = CheckThis
End If
'*************************************************
End Function```

6. Registered 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
right-hand 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,

7. Registered 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
Try this

Code:
```Function My_Code(CheckThis)
Dim firstbit As Double
Dim Nextup As Long
'the first bit
firstbit = 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)
Next

NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")

'*************************************************
My_Code = "Check Sum Error"
Else
My_Code = CheckThis
End If
'*************************************************
End Function```

Join Date
Feb 2004
Location
New Zealand
Posts
1,469
old 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 helps

9. Registered 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
'the first bit
firstbit = 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)

Next

NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")

'My_Code = CheckThis & " " & THISANSWER

'*************************************************

My_Code = "Not Valid"

Else

My_Code = "Valid"

End If

'*************************************************

End Function

10. Registered 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
'the first bit
firstbit = 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)

Next

NEXTTEN = Str(Val(Left(ANSWER, 1)) + 1 & "0")

Else
End If

'*************************************************
My_Code = "Not Valid"
Else
My_Code = "Valid"
End If
'*************************************************

End Function

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•