Converting Excel check digit formula to Access

[LarryN]

I am calculating the check digit for a 13 digit number in Excel and I need to be able to do it in Access. Any help will be greatly appreciated,


My Excel formula; =1&E1&MOD(-SUM(MID(1&E1&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14}, 1)*{3;1}),10) -- cell E1 holds the base 12 digit number.
My attempt to convert to Access; =1 & [txtBase] & [-SUM(MID("1" & [txtBase] &0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}) mod 10] -- txtBase holds the base 12 digit number

[June7]

I have no idea what a 'check digit' is. Please explain this in plain Engish.

It is possible to invoke and run Excel functions within VBA so you don't have to figure out a VBA substitute. I tested this with matrix multiplication. I found that the results weren't exactly the same between the 3 (Excel formula in spreadsheet cell, VBA invoking Excel, VBA substitute) but close enough.

http://bytes.com/topic/access/insigh...nctions-access

[LarryN]

Below is a very good description from Wikipedia. A check digit in this case is used to verify a GTIN (Global Trade Identification Number) which is like a UPC code on products you buy in the store. A UPC code with check digit is 12 digits where a GTIN with check digit is 14 digits.

A check digit is a form of redundancy check used for error detection on identification numbers (e.g. bank account numbers) which have been input manually. It is analogous to a binary parity bit used to check for errors in computer-generated data.

[June7]

Did you mean to reference a URL in post?

Did you look into invoking Excel functions?

Now explain the algorithm for the calculation you want (i.e., (a * b / c) - 15) or whatever. If you did this by pencil and paper, what steps would you follow? In a word, pseudocode.

[ssanfu]

It would have been nice to have a before and after example of a GTIN, but with what you provided and searching the web, I cobbled a function together.
In this function, the GTIN must be a string.

The function:

Public Function CalcGTIN(pGTIN As String) As String
    'http://www.gs1sy.org/GS1System/id_keys/CheckDigit.htm

    'the number to multiply by
    Const N As String = "131313131313"
    Const NumOfDigits As Integer = 12

    Dim i As Integer  'counter
    Dim ChkDigit As Long   '
    Dim txtBase As String  '
    Dim x As Long

    'set default return value
    CalcGTIN = "00000000000000"

    txtBase = Trim(pGTIN)

    If Len(txtBase) = NumOfDigits Then
        For i = 1 To NumOfDigits
            x = x + (Mid(txtBase, i, 1) * Mid(N, i, 1))
        Next

        ' next bigger number and subtract ChkNum
        ChkDigit = (x + (10 - (x Mod 10))) - x

        'return
        CalcGTIN = "1" & txtBase & ChkDigit
    End If

End Function

Call it using:

Sub TestGTIN()
    Dim sWithCheckdigit As String
    Dim TradingProcessNum As String

    TradingProcessNum = "629104150021"

    sWithCheckdigit = CalcGTIN(TradingProcessNum)


'this should be one line
    MsgBox "Original = " & TradingprocessNum & " - " & Len(TradingProcessNum) & " characters " & vbNewLine & vbNewLine & "GTIN = " & sWithCheckdigit & " - " & Len(sWithCheckdigit) & " characters "

End Sub

[LarryN]

Thank you I appreciate what you have done. This will work, I was hoping for something like what I am using for Excel that I could use in the Expression Builder.

[rpeare]

If ssnafu's function does what you want and you make it a public function you should be able to call it anywhere in your code/queries you want similar to the built in functions.

[LarryN]

That is what I plan on doing, thank you.