Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2006
    Posts
    28

    Question 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

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    this 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
    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 15:08.
    hope this help

    See clear as mud


    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
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

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

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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
    hope this help

    See clear as mud


    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
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Try 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

    See clear as mud


    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
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    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. #7
    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,


    Quote Originally Posted by myle
    Try 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

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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
    hope this help

    See clear as mud


    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
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    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

  10. #10
    Join Date
    Jan 2006
    Posts
    28

    Smile

    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

Posting Permissions

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