Solved: Check digit Calculation

[candie213]

Is there a formula to calculate the check digit in a query field in access?

I have a formula in excel that I use to manually create the check digit and import the product codes into a access table. The formula doesn't work in access. I found this excel spreadsheet online to calculate check digit. It uses this formula =Azalea_UPC_A_checkDigit(A1).

The following is the vba code:
Function Azalea_UPC_A_checkDigit(ByVal UPCnumber As String) As String
' 18dec08 Jerry Whiting
' Copyright 2008 Azalea Software, Inc. All rights reserved. www.azalea.com
' Your input, UPCnumber, is an 11 character string for a UPC version A barcode.
' This function calculates the check digit value for your 11-digit input.

Dim checkDigitSubtotal As Integer ' check digit throwaway

' Valid input is 11-digits (no check digit), 12-digits (with check digit), or 14-digits (GTIN).
Select Case Len(UPCnumber)
Case 11
' do check digit calculation
Case 12
' lets ignore your check digit & use ours
UPCnumber = Left(UPCnumber, 11)
Case 14
' GTIN input, strip leading 2 characters & last one too;
UPCnumber = Mid(UPCnumber, 3, 11)
Case Else
' error handling goes here
End Select

' do the check digit
checkDigitSubtotal = (Val(Left(UPCnumber, 1))) + (Val(Mid(UPCnumber, 3, 1))) + (Val(Mid(UPCnumber, 5, 1))) + (Val(Mid(UPCnumber, 7, 1))) + (Val(Mid(UPCnumber, 9, 1))) + (Val(Right(UPCnumber, 1)))
checkDigitSubtotal = (3 * checkDigitSubtotal) + (Val(Mid(UPCnumber, 2, 1))) + (Val(Mid(UPCnumber, 4, 1))) + (Val(Mid(UPCnumber, 6, 1))) + (Val(Mid(UPCnumber, 8, 1))) + (Val(Mid(UPCnumber, 10, 1)))
Azalea_UPC_A_checkDigit = Right(Str(300 - checkDigitSubtotal), 1)
End Function

[OBP]

In what way does it not work in Access?

[candie213]

I am trying to get this code into access and into a query to work. It works in excel. I am not sure how to get it to work in access.

[OBP]

Well, it is basically the same, the VBA Code needs to be Placed in to a VBA Module, to do so open the VBA Editor using Alt+f11 and on the main menu>Insert>Module.
Paste your code in to the Module.
Then you need to add a Column Heading which will use the Module.
See the attached database for how a query uses a Module. The query you need is "convertdate".

[candie213]

Thanks! It now works!