UPC calculating works in excel but not in access

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!

0
kyle775
5/18/2007 1:45:18 PM
access 16762 articles. 3 followers. Follow

9 Replies
1142 Views

Similar Articles

[PageSpeed] 23

On 18 May 2007 06:45:18 -0700, kyle775 <kyle.schmitz@gmail.com> wrote:

>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!

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
 
0
Armen
5/18/2007 1:55:27 PM
On May 18, 9:55 am, Armen Stein <ArmenSt...@R3m00v3Th1s.gmail.com>
wrote:
> On 18 May 2007 06:45:18 -0700, kyle775 <kyle.schm...@gmail.com> wrote:
>
>
>
> >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!
>
> 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

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.

0
kyle775
5/18/2007 2:03:02 PM
On May 18, 10:03 am, kyle775 <kyle.schm...@gmail.com> wrote:
> On May 18, 9:55 am, Armen Stein <ArmenSt...@R3m00v3Th1s.gmail.com>
> wrote:
>
>
>
> > On 18 May 2007 06:45:18 -0700, kyle775 <kyle.schm...@gmail.com> wrote:
>
> > >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!
>
> > 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
>
> 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.

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

0
kyle775
5/18/2007 2:53:47 PM
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
__________________________________________

"kyle775" wrote:

> Changing the reserved work Sum to UPCSum did not change the results.
0
Utf
5/18/2007 11:53:00 PM
On May 18, 7:53 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> 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
> __________________________________________
>
> "kyle775" wrote:
> > Changing the reserved work Sum to UPCSum did not change the results.

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.


0
kyle775
5/23/2007 2:57:09 PM
On May 18, 7:53 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> 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
> __________________________________________
>
> "kyle775" wrote:
> > Changing the reserved work Sum to UPCSum did not change the results.

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.

0
kyle775
5/23/2007 2:59:33 PM
Access is unreliable

you need access data projects in order to use UPC datatype


"kyle775" <kyle.schmitz@gmail.com> wrote in message 
news:1179932229.216854.221570@q75g2000hsh.googlegroups.com...
> On May 18, 7:53 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
>> 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
>> __________________________________________
>>
>> "kyle775" wrote:
>> > Changing the reserved work Sum to UPCSum did not change the results.
>
> 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.
>
> 

0
Tom
5/24/2007 12:14:15 AM
Everyone please note that Aaron  Kem.pf is attempting to impersonate one of 
our regular posters again.  Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact 
info.


"Tom Wickerath MDB" <tom_wickerath@hotmail.com> wrote in message 
news:OeJ9shZnHHA.4412@TK2MSFTNGP02.phx.gbl...
> Access is unreliable
>
> you need access data projects in order to use UPC datatype
>
>
> "kyle775" <kyle.schmitz@gmail.com> wrote in message 
> news:1179932229.216854.221570@q75g2000hsh.googlegroups.com...
>> On May 18, 7:53 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
>>> 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
>>> __________________________________________
>>>
>>> "kyle775" wrote:
>>> > Changing the reserved work Sum to UPCSum did not change the results.
>>
>> 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.
>>
>>
> 


0
69
5/24/2007 12:36:23 AM
On May 23, 8:14 pm, "Tom Wickerath MDB" <tom_wicker...@hotmail.com>
wrote:
> Access is unreliable
>
> you need access data projects in order to use UPC datatype
>
> "kyle775" <kyle.schm...@gmail.com> wrote in message
>
> news:1179932229.216854.221570@q75g2000hsh.googlegroups.com...
>
> > On May 18, 7:53 pm, Tom Wickerath <AOS168b AT comcast DOT net> wrote:
> >> 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...
> >> __________________________________________
>
> >> "kyle775" wrote:
> >> > Changing the reserved work Sum to UPCSum did not change the results.
>
> > 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_cal...
> > 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.

what does this mean? access data projects? is this a large under
taking?

0
kyle775
5/25/2007 1:19:29 PM
Reply:

Similar Artilces:

cannot access links in emails
This is the notice I get when I click on an email link. This operation has been cancelled due to restrictions in effect on this computer -- Thanks, Ronjon444 ronjon444 wrote: > This is the notice I get when I click on an email link. > This operation has been cancelled due to restrictions in effect on this > computer You are claiming that you have no knowledge of how to perform online searches? Google still works: http://www.google.com/search?q=%2B%22operation+has+been%22+cancelled+canceled+%2B%22due+to+restrictions+in+effect+on+this+computer%22 Operation ca...

Sorting by Color in Excel 2000
I want to sort text by color. I have a worksheet with some rows in black font and some in red. I need to group the rows in red font together. Is there anyway to sort by color? Thanks Hi see: http://www.cpearson.com/excel/SortByColor.htm not possible without VBA -- Regards Frank Kabel Frankfurt, Germany "Greg" <gregor1974@hotmail.com> schrieb im Newsbeitrag news:0ce901c4ac89$2be98dc0$a301280a@phx.gbl... > I want to sort text by color. I have a worksheet with > some rows in black font and some in red. I need to group > the rows in red font together. Is there anyw...

Incorrect formatting opening .csv file in Excel
When opening a database generated .csv file with the following data: "1","10E-024","1" Excel opens it incorrectly formatting the text as a scientific number: 1 1.0E-24 1 If I save the file as .txt, and open in excel using the import wizard, I can specify that the column should be text, and the item code 10E-024 appears correctly. However this is far from ideal, as it relies on a manual step and the file cannot be saved as .csv again - otherwise the same fault happens next time the file it opened. Is there a way of forcing Excel to open .csv files in raw da...

Syncronization of Access Database with Replica over the Net
Dear Professional. I want to Sycronize a database and its replica using the Net. Can you guide me in this regard. Best Wishes, Noor Mohammed Khan Try looking at the information here: http://www.dfenton.com/DFA/Replication/index.php?title=Main_Page -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com If my post was helpful to you, please rate the post. __________________________________ "eJirga@gmail.com" wrote: > Dear Professional. I want to Sycronize a database and its replica > using the Net. Can you guide me i...

Excel #21
I have another question for this paper that I'm working on and I can't seem to find the answer to. I'm a new user for Excel and I know next to nothing about it. Any help you can give me would be great... The question is: "If you see a spreadsheet with row AND column headings as NUMBERS, what is the problem (if any!!)? Tools > Options > General tab > uncheck R1C1 reference style > OK "allybell1980" wrote: > I have another question for this paper that I'm working on and I can't seem > to find the answer to. I'm a new user for Excel...

Merging Excel Sheet with Excel Sheet (Just like in MS Word!)
Hi everyone, Purpose: create classroom roster between one Excel spreadsheet as data source and another excel sheet. I know how to use one excel spreadsheet as a data source to merge with another word document; however, by using this method, I found that the link between the files keep dropping from time to time, especially when the folder has been moved around. I'm thinking of creating the same layout of the Word document in another Excel spreadsheet and somehow "LINK" the data source and the other spreadsheet together, but I'm not sure how, or whether thi...

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

OT: Accessing this NG via Usenet-News
Well, how lovely to discover that I've still got 5.7gb's left to use with Usenet-News, from a block that I bought year and years ago ! :-) I've now deleted msnews.microsoft.com from OE ....now lets' have a rummage..... regards, Richard From: "RJK" <nosuch@hotmail.com> | Well, how lovely to discover that I've still got 5.7gb's left to use with | Usenet-News, from a block that I bought year and years ago ! :-) | I've now deleted msnews.microsoft.com from OE | ...now lets' have a rummage..... | regards, Richard ...

Read from Barcode Reader in Excel and Auto hit enter
I am trying to read a barcode off an item, it then goes into it's place in the excel sheet but I cannot get it to automatically hit enter, I have to walk back to machine to hit it. As I recall (12 years ago...) there is a setting on the barcode reader to add Line Feed -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Neo" <Neo@discussions.microsoft.com> wrote in message news:FB95BB3A-91C8-4965-9528-7D32920B81EB@microsoft.com... >I am trying to read a barcode off an item, it then goes into it's place in > the excel sheet but I cannot ...

Excell 2002 xp
need to enable file sharing so that we can have version control on a Excel document Tools, Share Workbook, select (put a check mark into) the option "Allow changes by more than one user...." Then make choices in the Advanced tab as desired.... MRO "sarosh Tafti" <sarosh.tafti@mcdanalds.ca> wrote in message news:071401c38219$9bcc8110$a401280a@phx.gbl... > need to enable file sharing so that we can have version > control on a Excel document ...

How can I have an initial zero in an Excel cell?
I want to be able to enter times as eg 0845 but no matter what cell format I use, there is a problem, either that the initial zero is lopped off or that I am informed there is an error. Is there any way of solving this? try <format><cell><number><custom> enter 0000 and you will get a four digit display with an initial display with a leading zero. Note this is not a time function "ALupin" wrote: > I want to be able to enter times as eg 0845 but no matter what cell format I > use, there is a problem, either that the initial zero is lopped off or...

excel causing system to be in low system resource
Hi all. Whenever i open my excel application, i am always prompted low system resource and have to exit the application. However all my other office applications like word, access work fine when i open them. I face similar problems before and i reinstall excel to solve the problem. Is there a better solution which can solve this problem once and for all? Thks in advance ...

Change theme colors in Excel
Working in the Excel 2010 Beta version, but I think it's the same in 2007... How can I change the theme and/or standard colors? I can find the colors I want with "more colors" but I have to add to recent colors EACH time I use Excel. Any way to change default settings for the color palette? ...

Copy sheet in Excel
Hi Wonder if anyone of you encounter this problem before? When I try to copy worksheet in a workbook, the following prompt comes up, indicating name conflict. "A formula or sheet you want to move or copy contains the name 'aaaa', which already exists on the destination worksheet. Do you want to use this version of the name? - To use the name as defined in the destination sheet, click Yes - To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box" When I clicked yes, another prompt comes up indicating ...

Work out tithe in Money
I know I'm using a very old version but a brief search seems to suggest that the situation has not changed much since from 2001 so I'll still ask this question. Why does Microsoft Money not have a built in way of calculating tithe? I'm sure I'm not the only person left in the world that still believes that the first 10% of earnings should be given away (to God or whoever you believe it should go to). Is there a good way of calculating this in Money? All I want is an option that you can select which will total the total net income each month and calculate 10% of this va...

Access
�Como se establecen las relaciones en access? ...

unable to access OWA
Hi, We have a problem with OWA. We are using OWA for quite a time now and everything worked fine. Until this week, with no apparent reason we are unable to access the OWA-website. When going to the OWA-website, the page seems to load and you see the different buttons come up, but then it says "Loading..." and it stays there for the rest of time. Browsing the different forums and newsgroups haven't helpt us out yet. So any help or hint would be welcome. Config. Windows 2000 server with all service packs, exchange 2000 SP3 and the clients are Windows XP (some with and some with...

Accessing the schema type value from XSLT ...
Folks, Have any of you figured out a way in .NET 1.1 to access the scheme type of a Xml node from XSLT? Appreciate any pointers or code snippets that you can provide. After some extensive research (& head-scratching) I understand I need the PSVI(Post Schema Validated Infoset). I can't seem to figure out how to use PSVI with XSLT. Am I on the right track here? Thanks, -Naraendira -- NaraendiraKumar R. R. wrote: > Have any of you figured out a way in .NET 1.1 to access the scheme type of a > Xml node from XSLT? Appreciate any pointers or code snippets that you can >...

[Excel 2003] problem in files with pivottables after install Office Service Pack 3
Hi all. System: Windows XP Pro SP2, Office 2003 Professional + Service Pack 3 I have a problem with some xls files after install office service pack 3. With service pack 2 this files normal open. With service pack 3 after open file displays dialog (my translate from russian) "In workbook ... have contents which can not be read. Try restore contents of workbook ? If you trust source of this workbook, press button YES". If I press "No" - file not opens. If I press "Yes", displays next dialog. This dialog form content a list of corrections in file. Biggest pa...

how do i get "click on" in recieved e mails to work
WHEN I RECEIVE E MAILS WITH "CLICK ON" STATEMENTS TO GO TO SOME OTHER WEB SITE MY OUTLOOK JUST BRINGS UP A BLANK MICROSOFT INTERNET EXPLORER PAGE AND GOES NO WHERE. WHAT SETTINGS DO I HAVE TO CHANGE TO CAUSE THE "CLICK ON" TO GO TO THE INTENDED WEB SITES. WJONES1122 <WJONES1122@discussions.microsoft.com> wrote: > WHEN I RECEIVE E MAILS WITH "CLICK ON" STATEMENTS TO GO TO SOME OTHER > WEB SITE MY OUTLOOK JUST BRINGS UP A BLANK MICROSOFT INTERNET > EXPLORER PAGE AND GOES NO WHERE. WHAT SETTINGS DO I HAVE TO CHANGE TO > CAUSE THE "CLICK ON...

.msg file access violation in Outlook
Hi, I have a .msg file that was created using Outlook 2000 SP-3. I am able to open the file only once in every boot cycle. The rest of the time I get a shariung violation (when moving/deleting) or an access violation message when re-opening in Outlook. How do I solve this? Thanks. -- AKS "dee" wrote: > Hi, > > I have a .msg file that was created using Outlook 2000 SP-3. > > I am able to open the file only once in every boot cycle. The rest of > the time I get a shariung violation (when moving/deleting) or an access > violation message when re-op...

Mail Merge from Access with datasource being a password protected
I have an Access application distributed to users of Office 2003 and Office 2007 via a runtime front end. The application needs to trigger a Mail Merge which will use query results in back end, which is protected by a password. I have coded in VB to cope with this in an Office 2007 environment - but when running with Office 2003 Word is asking for the data source when initially opening the mail merge document from Accesss; when opened directly it does not ask for the data source and when responding with "No" to the SQL prompt it opens fine. Can anyone help? My code...

Using Access 2003 for Inventory
I plan to setup our inventory system in the warehouse using Access 2003. Now, what I would like it to do (and it has been a long time since I have tried to do anything with access so I don't remember if this is possible or not) is automatically create a report that will tell me when I am low on equipment and possibly e-mail it to me and my supervisor. I'd also like to do the samething so with our drop material (cables, ground blocks, etc.) and have it e-mail to the owner so that he can place an order. The system, ideally, will also allow me to view what our technicians have on ...

CRM 3.0: Unable to access CRM from the same machine
I've installed CRM 3.0 on a server with four IP addresses (one for the machine and one for each Web server hosted on the machine). The DNS server maps "crm" A record to the right IP address. >From clients, I can access the CRM server using http://crm/ >From the server, I can only access CRM web server using the IP address. If I use the http://crm/ URL I always get the authentication dialog box and authentication doesn't work. Any advise? Thanks, Andrea You may need to explicitly add the server alis to the intranet Zone sites list. -- Matt Parks MVP - Microsof...

Calculating cells
I have too many array formulas, and calculating cells takes to long that even freezes excel and stopped working. What I did is, from "options", tab "calculate", changed from "calculate automatic" to "manual calculations". After that excel works fine if I calculate cell by cell, BUT I do need to calculate more than 1 cell at a time. What I want to do is select a certain number of cells and then calculate them manually, and since they are array formulas I have to use ctrl+shift+enter. Do you know how to do that? -- asdf Mayb...