0

I created UPC check-digit function in excel which is working perfectly fine but i am now in need of creating same thing in MS Access.

here is the code:

Public Function CHECKDIGIT(UPC As String) As String

    If Len(UPC) = 8 Then

        CHECKDIGIT = WorksheetFunction.RoundUp(WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1)), -1) - (WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1)))

    ElseIf Len(UPC) = 12 Then

        CHECKDIGIT = WorksheetFunction.RoundUp(WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1)), -1) - (WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1)))

    ElseIf Len(UPC) = 13 Then

        CHECKDIGIT = WorksheetFunction.RoundUp(WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1)) + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1)) * 3, -1) - (WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1)) + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1)) * 3)

    ElseIf Len(UPC) = 14 Then

        CHECKDIGIT = WorksheetFunction.RoundUp(WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1), Mid(UPC, 13, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1)), -1) - (WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1), Mid(UPC, 13, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1)))

    ElseIf Len(UPC) = 17 Then

        CHECKDIGIT = WorksheetFunction.RoundUp(WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1), Mid(UPC, 13, 1), Mid(UPC, 15, 1)) + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1), Mid(UPC, 14, 1), Mid(UPC, 16, 1)) * 3, -1) - (WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1), Mid(UPC, 13, 1), Mid(UPC, 15, 1)) + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1), Mid(UPC, 14, 1), Mid(UPC, 16, 1)) * 3)

    ElseIf Len(UPC) = 18 Then

        CHECKDIGIT = WorksheetFunction.RoundUp(WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1), Mid(UPC, 13, 1), Mid(UPC, 15, 1), Mid(UPC, 17, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1), Mid(UPC, 14, 1), Mid(UPC, 16, 1)), -1) - (WorksheetFunction.Sum(Mid(UPC, 1, 1), Mid(UPC, 3, 1), Mid(UPC, 5, 1), Mid(UPC, 7, 1), Mid(UPC, 9, 1), Mid(UPC, 11, 1), Mid(UPC, 13, 1), Mid(UPC, 15, 1), Mid(UPC, 17, 1)) * 3 + WorksheetFunction.Sum(Mid(UPC, 2, 1), Mid(UPC, 4, 1), Mid(UPC, 6, 1), Mid(UPC, 8, 1), Mid(UPC, 10, 1), Mid(UPC, 12, 1), Mid(UPC, 14, 1), Mid(UPC, 16, 1)))

    ElseIf Len(UPC) <> 8 Or Len(UPC) <> 12 Or Len(UPC) <> 13 Or Len(UPC) <> 14 Or Len(UPC) <> 17 Or Len(UPC) <> 18 Then

        CHECKDIGIT = "MISSING DIGITS"

    End If
End Function

Is there any easy way to convert this to MS Access? To my knowledge not all formulas will transfer directly to access so I image I will need to build custom function in Access too.

Any idea how to go with this?

Thanks, Slav

Improve this question
1
  • 1
    WorksheetFunction.Sum(x, y) = x + y, and WorksheetFunction.RoundUp(x) = Round(x + 0.5). Everything else is the same. Commented Sep 19, 2016 at 22:47

2 Answers 2

Reset to default
1

The following code should work equally well in Excel and Access:

Public Function CHECKDIGIT(UPC As String) As String
    Dim n As Integer
    Dim i As Integer

    Select Case Len(UPC)
        Case 8, 12, 13, 14, 17, 18
            n = 0
            For i = 1 To Len(UPC) - 1
                If ((Len(UPC) - i) Mod 2) = 1 Then
                    n = n + CInt(Mid(UPC, i, 1)) * 3
                Else
                    n = n + CInt(Mid(UPC, i, 1))
                End If
            Next
            CHECKDIGIT = CStr(Int(n / 10 + 0.99) * 10 - n)

        Case Else

            CHECKDIGIT = "MISSING DIGITS"

    End Select
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Ok perfect. This one is working like a charm. Can i use this as an function in Access? Like in excel i would just type =CHECKDIGIT(A1) but in access i know it will be different. Should i see this as part of built in function in Access?
I figured it out. I just call it within query or a form. YowE3K .....man you rock. this is awesome. Thank you so much.
@Slavisha - man you rock - hmm, usually when people refer to both me and rocks in the same sentence it is with the expression "YowE3K has rocks in his head".
No :-) in my case you actually saved me so much effort. If you are ever in Atlanta... you have a drink on me. Now im going to study your logic. I have bit hard time understanding what exactly you did there line by line. it gives same results as my function but your is way more elegant.
Mine is just looking at each character (except the last, checkdigit, character), deciding whether it is an even or odd position, and then deciding therefore whether to multiply by 1 or 3. The sum of those can then be deducted from the next higher multiple of 10 to get the checkdigit. (Actually, I just realised that the last step could be CHECKDIGIT = CStr((1000 - n) Mod 10) instead of CHECKDIGIT = CStr(Int(n / 10 + 0.99) * 10 - n) - that might be simpler.)
|
0

Thank you for the code YowE3K. I think there is something missing in that code. When i run it against results i get using my code It fails.

UPC CHCKDIGIT   NEW CHKDIGIT
018371009355    5   5
00018371009355  5   5
7501206634004   4   10
00018371019354  4   14
018371019354    4   14

Also by using function i just found out that i cant use the function as part of data entry validation. I will have to use formula using what Comintern suggested above.

Thank you both guys for helping with this.

Improve this answer

1 Comment

I see I had two errors in my code - (a) I hadn't noticed the difference in the function between even and odd length UPC codes, and (b) I should have been using an Int instead of a Round. I fixed both errors in my answer, just in case anyone else needs a UPC checkcode function in the future.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.