candie213
03-31-2011, 07:10 AM
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 (http://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
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 (http://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