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
10 6631
Try: - checkDigitSubtotal = ((Val(Left(ISBN, 1))) * 10 + (Val(Mid(ISBN, 2, 1))) * 9 + ......
OldBirdMan - I tried that and it still isn't working properly.
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 8,834
Recognized Expert Expert
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
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 8,834
Recognized Expert Expert
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
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 8,834
Recognized Expert Expert
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.
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 8,834
Recognized Expert Expert - Open qryCalculateCheckDigit 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.
- BarbQb, look at the Attachment. The Function, ISBN_10(), is defined in a Standard Code Module (Module1) as 'Public'.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: William C. White |
last post by:
Does anyone know of a way to use PHP /w Authorize.net AIM without using
cURL? Our website is hosted on a shared drive and the webhost company
doesn't installed additional software (such as cURL)...
|
by: Albert Ahtenberg |
last post by:
Hello,
I don't know if it is only me but I was sure that header("Location:url")
redirects the browser instantly to URL, or at least stops the execution of
the code. But appearantely it continues...
|
by: James |
last post by:
Hi,
I have a form with 2 fields.
'A'
'B'
The user completes one of the fields and the form is submitted.
On the results page I want to run a query, but this will change
subject to which...
|
by: Ollivier Robert |
last post by:
Hello,
I'm trying to link PHP with Oracle 9.2.0/OCI8 with gcc 3.2.3 on a Solaris9
system. The link succeeds but everytime I try to run php, I get a SEGV from
inside the libcnltsh.so library.
...
|
by: Richard Galli |
last post by:
I want viewers to compare state laws on a single subject.
Imagine a three-column table with a drop-down box on the top. A viewer
selects a state from the list, and that state's text fills the...
|
by: Albert Ahtenberg |
last post by:
Hello,
I have two questions.
1. When the user presses the back button and returns to a form he filled
the form is reseted. How do I leave there the values he inserted?
2. When the...
|
by: inderjit S Gabrie |
last post by:
Hi all
Here is the scenerio ...is it possibly to do this...
i am getting valid course dates output on to a web which i have designed
....all is okay so far , look at the following web url
...
|
by: Jack |
last post by:
Hi All,
What is the PHP equivilent of Oracle bind variables in a SQL statement, e.g.
select x from y where z=:parameter
Which in asp/jsp would be followed by some statements to bind a value...
|
by: Sandwick |
last post by:
I am trying to change the size of a drawing so they are all 3x3.
the script below is what i was trying to use to cut it in half ... I
get errors.
I can display the normal picture but not the...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |