CODEWITHSUNDEEP
ms-accessvba
mibacode
mibacode

Reputation: 404

Access VBA giving incorrect results when comparing two numbers

I have a table linked to a form where a user enters an ISBN number. I'm checking to make sure the ISBN is valid. However, when I get to a point where I need to compare two numbers, I am incorrectly told that they do not match, when they definitely do match.

Private Sub isbn_BeforeUpdate(Cancel As Integer)

Dim isbn As String
Dim cleanIsbn As Double
Dim onlyIsbn As Double
Dim checkDigit As Integer
Dim legnth As Integer

length = 0
isbn = Forms!frmPubInfo!isbn.Value

' Strip out all hyphens
For i = 1 To Len(isbn)
    Dim ch As String
    ch = Mid(isbn, i, 1)
    If IsNumeric(ch) Then
        length = length + 1
        Dim num As Integer
        num = CInt(ch)
        cleanIsbn = (cleanIsbn * 10) + num
    End If
Next

' Check if 13 numbers
If length = 13 Then
    Dim xBy3 As Boolean
    Dim total As Integer
    Dim calcCheckDigit As Integer

    total = 0
    xBy3 = False

    ' Calculate total amount
    For j = 1 To 12
        ch = Mid(cleanIsbn, j, 1)
        If xBy3 = True Then
            total = total + (ch * 3)
            xBy3 = False
        Else
            total = total + ch
            xBy3 = True
        End If
    Next

    ' Get calculated check digit
    calcCheckDigit = 10 - (total Mod 10)

    ' Extract check digit
    checkDigit = Mid(cleanIsbn, 13, 1)

    ' Debug output
    MsgBox ("Actual CheckDigit: " & checkDigit & vbNewLine & _
    "Calculated CheckDigit: " & calcCheckDigit)

    ' Check if check digit and calculated check digit match
    If checkDigit <> calculatedCheckDigit Then
        MsgBox ("checkDigit and calcCheckDigit are not the same")
    Else
        MsgBox ("They match! ISBN is good!")
    End If

Else
    ' Display error
    MsgBox ("Not enough numbers!")
End If

End Sub

When I get down to the 'Check if check digit and calculated check digit match', the If statement always says they don't match, even though the debug output above gives me the same two numbers.

I have tried:

I initially thought it was an issue with the data types, but if I'm casting them to the same type right as I'm comparing them, that can't be the issue, right?

This is all using Access 2013.

Upvotes: 2

Views: 1577

Answers (1)

Matt
Matt

Reputation: 46720

Man... I can't believe I didn't see this at first. I tested your code and got similar results as you. After looking at it closer I noticed that the if statement was wrong. In short you need to use Option Explicit and this error would have been caught. Option Explicit ensures that all variables are declared. In not an error is thrown.

You statement contains a null

If checkDigit <> calculatedCheckDigit Then

You dont have a variable called calculatedCheckDigit it should be calcCheckDigit. 

If checkDigit <> calcCheckDigit Then

Just a side note: Your code for stripping out the hyphens obviously works but I offer this tweak.

' Dim as string since it is treated as one with Mid anyway. 
' In practice Len didnt give accurate results while it was Double. 
Dim cleanIsbn As String

' Strip out all hyphens
cleanIsbn = Replace(isbn, "-", "")

' Check if 13 numbers
If (Len(cleanIsbn) = 13) And IsNumeric(cleanIsbn) Then
    ' Process Stuff
Else
    ' Display error
     MsgBox "Does not appear to be a valid ISBN value!"
End If

Take the isbn and just remove the hyphens with a replace. After that change if the result cleanIsbn is 13 characters long and numeric then you can assume its a good value to process.

ISBN Reference

I had to look it up but the math behind the ISBN numbers is located for reference here

Upvotes: 3

Related Questions