How to use Mod 11

[BarbQb]

Hi All.

I am trying to write a function to calculate the ISBN check digit and I cannot get my code to give the correct output.

The ISBN is calculated by weighting the numbers, dividing by 11 and the remainder is the check digit. Below is what I have, but Access is not returning the correct check digit.

The user enters a 9 digit number into a text box, presses a command button and the check digit is supposed to be returned.


Any help would be great. Thanks.

Function ISBN_10(ByVal ISBN As String) As String

Dim checkDigitSubtotal As Integer 

' Possible valid input includes 9-digits (no check digit)
Select Case Len(ISBN)
Case 9
' We're going to do the ISBN check digit calculation because the input doesn't have it.
Case Else
MsgBox ("You must enter 9 numbers")

End Select

checkDigitSubtotal = ((Val(Left(ISBN, 1))) * 10 + (Val(Left(ISBN, 2, 1))) * 9 + (Val(Mid(ISBN, 3, 1))) * 8 + (Val(Mid(ISBN, 4, 1))) * 7 + (Val(Mid(ISBN, 5, 1))) * 6 + (Val(Mid(ISBN, 6, 1))) * 5 + (Val(Mid(ISBN, 7, 1))) * 4 + (Val(Mid(ISBN, 8, 1))) * 3 + (Val(Mid(ISBN, 9, 1))) * 2) Mod 11

ISBN_10 = checkDigitSubtotal

End Function

Command Button:

Private Sub Command15_Click()

Me.Text13 = ISBN_10(Me.Text12)

Me.Text14 = [Text12] & [Text13]

End Sub

[OldBirdman]

Try:

checkDigitSubtotal = ((Val(Left(ISBN, 1))) * 10 + (Val(Mid(ISBN, 2, 1))) * 9 + ......

[BarbQb]

OldBirdMan - I tried that and it still isn't working properly.

[OldBirdman]

I ran:

strX = "111111111"
strX = ISBN_10(strX)
Debug.Print strX

and got 10, which is (10+9+8+7+6+5+4 +3+2) = 54 mod 11 = 10 derived from your formula.
When strX = "222222222" the result is 9 and strX = "020000006" is (18+12)=30 mod 11 = 8
I have no sample data supplied by you, nor do I know what is wrong, only that it "... still isn't working properly." I saw an apparent error / typo and suggested correcting it. If I look further, I note that Mod 11 will give answers from 0 to 10 inclusive. I ask myself how a check digit can be two digits. Maybe its a check field, but not a check digit. Or maybe the formula is wrong.
A check of the internet with Google says you are not using the correct formula. For ISBN 10 - "The final character of a ten digit International Standard Book Number is a check digit computed so that multiplying each digit by its position in the number (counting from the right) and taking the sum of these products modulo 11 is 0. The furthest digit to the right (which is multiplied by 1) is the check digit, chosen to make the sum correct."
So when you do the Mod 11, you have to subtract the result from 11, unless the result was 0.
In your first post, line 16 & 17 of your code should be:

If checkDigitSubtotal <> 0 then checkDigitSubtotal = 11 - checkDigitSubtotal 
If checkDigitSubtotal = 10 Then ISBN_10 = "X" Else ISBN_10 = checkDigitSubtotal

You are going to have to deal with the "X" returned by your revised function if you allow codes that make "n Mod 11 = 10"
Reference: WikipediA Check Digit

[ADezii]

Here is a little Function I created for you that will get the job done. It is a little clumsy, and if I have the chance, I'll refine it later. Any questions, feel free to ask.

Function ISBN_10(ByVal ISBN As String) As Variant
Dim intVal As Integer
Dim intPosition As Integer
Dim checkDigitSubtotal As Long
Dim intCtr As Integer

If Len(ISBN) <> 9 Or Not IsNumeric(ISBN) Then Exit Function

intPosition = 0     'Initialize

For intVal = 10 To 2 Step -1
  intPosition = intPosition + 1
    checkDigitSubtotal = checkDigitSubtotal + Val(Mid(ISBN, intPosition, 1)) * intVal
      Debug.Print checkDigitSubtotal      'Let's see what is going on!
Next

'Let's compute the Check Digit. It will be a Number between 0 and 10
'which, when added to the Accumulated Total, will be evenly divisible
'by 11, namely (X Mod 11 = 0). If this Digit happens to be a 10, then
'the Function must return 'X' which will be appended to the 9 Digits
'instead of a Value between 0 and 9.
For intCtr = 0 To 10
  If (checkDigitSubtotal + intCtr) Mod 11 = 0 Then
    If intCtr = 10 Then
      ISBN_10 = "X"
    Else
      ISBN_10 = intCtr
    End If
    Exit For
  End If
Next
End Function

[BarbQb]

Thank you both so much. I really appreciate your help with this problem. I realize now I was missing a key step. 11 - CDSubtotal Mod 11

OldBirdman - sorry I wasn't specific before about the problem I was encountering.

ADezii - thank you for the code. I will try it out as soon as I can.

[ADezii]

Here is the logical counterpart to the code that I previously wrote. Simply pass to the Function a Formatted/Non-Formatted ISBN Number, and you will be notified as to whether or not the ISBN is valid. Good luck - any questions feel free to ask.

Public Function fIsISBNValid(ByVal strISBN As String) As Boolean
Dim intVal As Integer
Dim intPosition As Integer
Dim checkDigitSubtotal As Long
Dim intCtr As Integer

strISBN = Replace(strISBN, "-", "")       'Strip any '-'s
  
If Len(strISBN) <> 10 Or Not IsNumeric(strISBN) Then
  fIsISBNValid = False
    Exit Function
End If
  
intPosition = 0     'Initialize
  
For intVal = 10 To 1 Step -1
  intPosition = intPosition + 1
    If intPosition = 10 And Right$(strISBN, 1) = "X" Then
      checkDigitSubtotal = checkDigitSubtotal + 10
    Else
      checkDigitSubtotal = checkDigitSubtotal + Val(Mid(strISBN, intPosition, 1)) * intVal
    End If
      Debug.Print checkDigitSubtotal
Next
  
If (checkDigitSubtotal Mod 11) = 0 Then
  fIsISBNValid = True
Else
  fIsISBNValid = False
End If
End Function

Dim strMyISBN As String

'strMyISBN = "1234567832"           'Invalid ISBN
'strMyISBN = "2137689057"            'Valid ISBN

'strMyISBN = "1931841047"            'Valid ISBN
strMyISBN = "1-931841-04-7"            'Valid ISBN (Formatted)

If fIsISBNValid(strMyISBN) Then
  MsgBox "[" & strMyISBN & "] is a VALID ISBN Number", vbInformation, "ISBN Verifier"
Else
  MsgBox "[" & strMyISBN & "] is a NOT a VALID ISBN Number", vbCritical, "ISBN Verifier"
End If

[BarbQb]

ADezii - Thank you again. This is really above and beyond :) I do have one question: I have this fucntion as a check for new records, but I would like to run a query that would calculate the checkdigit in my existing tables. How would I incorporate this Function in query?

[ADezii]

Let's assume that you have a Table named Table1, and that it contains a TEXT Field named ISBN. The following Query will display the 9-Digit ISBN along with the Calculated Check Digit in a Field named Check_Digit. The SQL, along with the Output resulting from sample Data, is listed below for your convenience:

SELECT [ISBN], ISBN_10([ISBN]) As Check_Digit FROM Table1;

ISBN	        Check_Digit
123456789	        X
234567891	        7
345678901	        7
456789012	        4
567890123	        0
876442134	        1
789342355	        2
988888822	        6
234	             Null
edefererer	      Null
443245692	        2

P.S. - Download the Attachment(ISBN .zip), everything you need is in this Demo.

[BarbQb]

ADezii - Sorry if I'm being obtuse, but how does the query reference the function ISBN_10()? Where should I have the function? Right now I have it in the VBA module for FormISBN, but the code works just for that form.

[ADezii]

1. Open qryCalculateChe ckDigit in Design View (Attachment) where you will see exactly how the Function is referenced via the Calculated Field [Check_Digit]. In you click on View, SQL while in Design View, you will see the SQL I posted in Post #9.
2. BarbQb, look at the Attachment. The Function, ISBN_10(), is defined in a Standard Code Module (Module1) as 'Public'.