Calculating UPCA Check Digit

[J Hohe]

I have a Access query that pulls a UPC code from a field in a linked SQL
table in our company data base (Great Plains) I have a number with 11 digits,
and I need to generate a 12th digit which is called a check digit. I have a
formula by which I can calculate this check digit in Excel, but I wish to do
all this all within my Access Query. Can anyone help a novice Access user
thru this?

 

[Jeff Boyce]

You don't mention where you need to use this check digit. If there's a
"formula by which [you] can calculate it", you probably don't need to store
it!

You mention a query ... you can add a new field in query design view and use
an expression (i.e., "calculation") as part of the output of your query.
For example, if you had a (greatly simplified) need to concatenate two
fields, you could use something like (untested, example only):
FullName: [LastName] & ", " & [FirstName]
in an empty "field" in your query design view.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[J Hohe]

I currently have an Access database linked to SQL Data that has a query
pulling the value for the UPC code (less the check digit). My desire is to
create a Check Digit calculator in Access and to concatenate two fields into
a new field "UPC w/CHK"

I have it in Excel, where it goes like this...

=MOD(10-(MOD(((DIGIT1+DIGIT3+DIGIT5+DIGIT7+DIGIT9+DIGIT11)
*3)+(DIGIT2+DIGIT4+DIGIT6+DIGIT8+DIGIT10),10)),10)

If I name the fields the same, what should my Access expression look like?

You don't mention where you need to use this check digit. If there's a
"formula by which [you] can calculate it", you probably don't need to store
it!

You mention a query ... you can add a new field in query design view and use
an expression (i.e., "calculation") as part of the output of your query.
For example, if you had a (greatly simplified) need to concatenate two
fields, you could use something like (untested, example only):
FullName: [LastName] & ", " & [FirstName]
in an empty "field" in your query design view.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a Access query that pulls a UPC code from a field in a linked SQL
table in our company data base (Great Plains) I have a number with 11
digits,
and I need to generate a 12th digit which is called a check digit. I have
a
formula by which I can calculate this check digit in Excel, but I wish to
do
all this all within my Access Query. Can anyone help a novice Access user
thru this?

 

[Jeff Boyce]

If you have separate fields for each digit, you have ... a spreadsheet, not
a well-normalized Access database!

The functions in Excel may or may not exist in Access, and may or may not
have exactly the same names and/or syntax.

Based on the formula you gave as an example, it looks like you are adding
some digits, multiplying by 3, subtracting from 10, adding other digits, and
using the MOD() function to get a "remainder".

If you understand how the algorithm works, you can find comparable functions
in Access (e.g., Access has a MOD() function), including adding, subtracting
and multiplying.

I'm still not clear on what you expect from this newsgroup. Are you looking
for someone to write the code for you?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I currently have an Access database linked to SQL Data that has a query
pulling the value for the UPC code (less the check digit). My desire is
to
create a Check Digit calculator in Access and to concatenate two fields
into
a new field "UPC w/CHK"

I have it in Excel, where it goes like this...

=MOD(10-(MOD(((DIGIT1+DIGIT3+DIGIT5+DIGIT7+DIGIT9+DIGIT11)
*3)+(DIGIT2+DIGIT4+DIGIT6+DIGIT8+DIGIT10),10)),10)

If I name the fields the same, what should my Access expression look like?

You don't mention where you need to use this check digit. If there's a
"formula by which [you] can calculate it", you probably don't need to
store
it!

You mention a query ... you can add a new field in query design view and
use
an expression (i.e., "calculation") as part of the output of your query.
For example, if you had a (greatly simplified) need to concatenate two
fields, you could use something like (untested, example only):
FullName: [LastName] & ", " & [FirstName]
in an empty "field" in your query design view.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a Access query that pulls a UPC code from a field in a linked SQL
table in our company data base (Great Plains) I have a number with 11
digits,
and I need to generate a 12th digit which is called a check digit. I
have
a
formula by which I can calculate this check digit in Excel, but I wish
to
do
all this all within my Access Query. Can anyone help a novice Access
user
thru this?

 

[John W. Vinson]

If I name the fields the same, what should my Access expression look like?

((DIGIT1+DIGIT3+DIGIT5+DIGIT7+DIGIT9+DIGIT11) *3 MOD 10 +
(DIGIT2+DIGIT4+DIGIT6+DIGIT8+DIGIT10) MOD 10) MOD 10

In Excel, MOD is a function taking two arguments in parentheses; in Access
it's an operator that works like + or * - e.g. 13 MOD 10 is 3.