UPC calculating works in excel but not in access

K

kyle775

Hi, ive build a query using the following module to calculate the
check digit (using mod 10) of an 11 digit upc number. In excel i can
use the same module to calculate the check digit on a 13 digit upc
code but in access it doesnt work.
Here is the vba (i didnt write it but found it to use on the internet)

Option Compare Database

Function Format_Upc_String(InString As String) As String
Dim OutString As String
Dim Multiplier As Integer, Sum As Integer, i As Integer
Dim CheckDigit As Integer
'
' Initialize the sum to zero
'
Sum = 0
'
' Add up the values of digits in the odd-numbered positions
'
For i = 1 To Len(InString) Step 2
Sum = Sum + Val(Mid$(InString, i, 1))
Next i
'
' Multiply this result by 3, then add in the values of
' the digits in the even-numbered positions
'
Sum = Sum * 3
For i = 2 To Len(InString) Step 2
Sum = Sum + Val(Mid$(InString, i, 1))
Next i
'
' Now calculate the Modulo 10 check digit
'
CheckDigit = Sum Mod 10
CheckDigit = 10 - CheckDigit
If CheckDigit = 10 Then
CheckDigit = 0
End If
OutString = InString + Format$(CheckDigit)
Format_Upc_String = OutString
End Function


Any help would be great!
 
A

Armen Stein

Hi, ive build a query using the following module to calculate the
check digit (using mod 10) of an 11 digit upc number. In excel i can
use the same module to calculate the check digit on a 13 digit upc
code but in access it doesnt work.
Here is the vba (i didnt write it but found it to use on the internet)

Option Compare Database

Function Format_Upc_String(InString As String) As String
Dim OutString As String
Dim Multiplier As Integer, Sum As Integer, i As Integer
Dim CheckDigit As Integer
'
' Initialize the sum to zero
'
Sum = 0
'
' Add up the values of digits in the odd-numbered positions
'
For i = 1 To Len(InString) Step 2
Sum = Sum + Val(Mid$(InString, i, 1))
Next i
'
' Multiply this result by 3, then add in the values of
' the digits in the even-numbered positions
'
Sum = Sum * 3
For i = 2 To Len(InString) Step 2
Sum = Sum + Val(Mid$(InString, i, 1))
Next i
'
' Now calculate the Modulo 10 check digit
'
CheckDigit = Sum Mod 10
CheckDigit = 10 - CheckDigit
If CheckDigit = 10 Then
CheckDigit = 0
End If
OutString = InString + Format$(CheckDigit)
Format_Upc_String = OutString
End Function


Any help would be great!
Click to expand...

You may want to post exactly what you are seeing; "doesn't work"
doesn't give us much to go on.

I'm seeing at least one reserved word: Sum. Try changing it to
something more specific, like UPCSum, and see if you get any further.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
K

kyle775

You may want to post exactly what you are seeing; "doesn't work"
doesn't give us much to go on.

I'm seeing at least one reserved word: Sum. Try changing it to
something more specific, like UPCSum, and see if you get any further.

Armen Stein
Microsoft Access MVPwww.JStreetTech.com
Click to expand...

Oh, when i say doesnt work, the check digit is not calculated. For
example giving this function a 13 digit "string" should return a 14
digit one. In my case it simply returns the same 13 digit number it
was given.
However, this is not the case if i give it an 11 digit number, in this
case it will return the correctly calculated 12 digit code.
 
K

kyle775

Oh, when i say doesnt work, the check digit is not calculated. For
example giving this function a 13 digit "string" should return a 14
digit one. In my case it simply returns the same 13 digit number it
was given.
However, this is not the case if i give it an 11 digit number, in this
case it will return the correctly calculated 12 digit code.
Click to expand...

Changing the reserved work Sum to UPCSum did not change the results.
 
G

Guest

Hi Kyle,

That was still a good change to make, even if it did not solve the problem
at hand. You should always avoid using reserved words when working with
Access. Here is a handy list for you to consult in the future:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I can't say that I know a darned thing about UPC codes, but I did a Google
search and found this site, which seems to be quite helpful:

UPC Check Digit
http://www.cs.queensu.ca/~bradbury/checkdigit/upccheck.htm

It *appears* as if your code is not calculating Step 3 correctly. Add a few
debug.print statements to see what I mean:

For i = 2 To Len(InString) Step 2
Debug.Print Val(Mid$(InString, i, 1))
UPCSum = UPCSum + Val(Mid$(InString, i, 1))
Next i

Using the UPC code example given on Jeremy Bradbury's web site, ie:
?Format_Upc_String("064200115896")

Step 3 should apparently calculate out to a result of 17. However, in your
case, it calculates to 23, because it is getting an extra 6 in the result.
From the Immediate Window, I see this result printed:

6
2
0
1
8
6

which doesn't quite jive with the example, as shown in step 3.

It would have been very helpful to me if you had provided some sample codes,
along with the checksums that you expect to calculate. For the present time,
this adjustment, To Len(InString) - 1, seems to work, but, like I said, I'm
really not familiar with UPC codes or calculation of checksums:

For i = 2 To Len(InString) - 1 Step 2
Debug.Print Val(Mid$(InString, i, 1))
UPCSum = UPCSum + Val(Mid$(InString, i, 1))
Next i


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
K

kyle775

Hi Kyle,

That was still a good change to make, even if it did not solve the problem
at hand. You should always avoid using reserved words when working with
Access. Here is a handy list for you to consult in the future:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I can't say that I know a darned thing about UPC codes, but I did a Google
search and found this site, which seems to be quite helpful:

UPC Check Digit
http://www.cs.queensu.ca/~bradbury/checkdigit/upccheck.htm

It *appears* as if your code is not calculating Step 3 correctly. Add a few
debug.print statements to see what I mean:

For i = 2 To Len(InString) Step 2
Debug.Print Val(Mid$(InString, i, 1))
UPCSum = UPCSum + Val(Mid$(InString, i, 1))
Next i

Using the UPC code example given on Jeremy Bradbury's web site, ie:
?Format_Upc_String("064200115896")

Step 3 should apparently calculate out to a result of 17. However, in your
case, it calculates to 23, because it is getting an extra 6 in the result.
From the Immediate Window, I see this result printed:

6
2
0
1
8
6

which doesn't quite jive with the example, as shown in step 3.

It would have been very helpful to me if you had provided some sample codes,
along with the checksums that you expect to calculate. For the present time,
this adjustment, To Len(InString) - 1, seems to work, but, like I said, I'm
really not familiar with UPC codes or calculation of checksums:

For i = 2 To Len(InString) - 1 Step 2
Debug.Print Val(Mid$(InString, i, 1))
UPCSum = UPCSum + Val(Mid$(InString, i, 1))
Next i

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Click to expand...

Thanks for that, very helpful.
It seemed odd to me that it was calculating correctly in excel, i have
used excel for tracking out items and thier upc codes until now, its
getting to be too slow and cumbersome.
Access should calculate in the same way?
UPC codes have several lengths etc, i call them upc codes but really
they have all sorts of fancy names. GTIN EAN-14. The 14 digit number
is really just the 11 digit number with two numbers added to the
front (to denote the packaging level) and then the check digit
calcualted on those 14 digits. The 12 digit number is just the number
with 11 digit upc with a check digit calcualted.
here is GS1's website
http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html
this is an international organization monitoring standards and company
prefixes
for upc codes.
here is an example of for numbers
77051921623
770519216239
2077051921623
20770519216233

and another set

77051921620
770519216208
2077051921620
20770519216202

thanks again.
 
K

kyle775

Hi Kyle,

That was still a good change to make, even if it did not solve the problem
at hand. You should always avoid using reserved words when working with
Access. Here is a handy list for you to consult in the future:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

I can't say that I know a darned thing about UPC codes, but I did a Google
search and found this site, which seems to be quite helpful:

UPC Check Digit
http://www.cs.queensu.ca/~bradbury/checkdigit/upccheck.htm

It *appears* as if your code is not calculating Step 3 correctly. Add a few
debug.print statements to see what I mean:

For i = 2 To Len(InString) Step 2
Debug.Print Val(Mid$(InString, i, 1))
UPCSum = UPCSum + Val(Mid$(InString, i, 1))
Next i

Using the UPC code example given on Jeremy Bradbury's web site, ie:
?Format_Upc_String("064200115896")

Step 3 should apparently calculate out to a result of 17. However, in your
case, it calculates to 23, because it is getting an extra 6 in the result.
From the Immediate Window, I see this result printed:

6
2
0
1
8
6

which doesn't quite jive with the example, as shown in step 3.

It would have been very helpful to me if you had provided some sample codes,
along with the checksums that you expect to calculate. For the present time,
this adjustment, To Len(InString) - 1, seems to work, but, like I said, I'm
really not familiar with UPC codes or calculation of checksums:

For i = 2 To Len(InString) - 1 Step 2
Debug.Print Val(Mid$(InString, i, 1))
UPCSum = UPCSum + Val(Mid$(InString, i, 1))
Next i

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Click to expand...

Sorry i tried the code you provided, and i get the same result. I am
noticing now that when i pass a 13 digit string, it is a calcualted
field in my query. Will that have an affect?
the only number that is stored in a table is the first 11 digit
number. the rest use concatenation and the module i provided to
create the last 3 codes.
 
T

Tom Wickerath MDB

Access is unreliable

you need access data projects in order to use UPC datatype
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UDF for Data into 128 B barcode problem 1
UDF not working. 3
Can some one please look at this code and see if there is a better way? 3
Help converting a vb6 function to vbnet. 8
Permutations andText to column problem 6
Business Object Problem 3
Help With Business Objects 4
FormCode - Converting Type-E UPC to Type-A (barcodes) 2

Top