UPC check digit?

[taunt]

Hello I'm trying to transfer a SQL query to Access and having no luck.
I was wondering if someone can post a query to add the check digit to
11 and 12 digit UPCs. I'm finding out that Access doesn't like
SUBSTRING commands or len commands (I was looking for (LEN(UPC) = 11)
and it errors out. Any help would be great.

Thanks

 

[John W. Vinson]

Hello I'm trying to transfer a SQL query to Access and having no luck.
I was wondering if someone can post a query to add the check digit to
11 and 12 digit UPCs. I'm finding out that Access doesn't like
SUBSTRING commands or len commands (I was looking for (LEN(UPC) = 11)
and it errors out. Any help would be great.

Thanks

The Access not-quite-equivalent of SUBSTRING is MID. Len() should work. Could
you post your actual expression and the error you're getting?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

The Access not-quite-equivalent of SUBSTRING is MID. Len() should work. Could
you post your actual expression and the error you're getting?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Well the database I'm doing it on has 11 digit UPCs, and 12. Here's
the coding:
INSERT INTO UPCR (ID, UPC, UPC1, UPC2, UPC3, UPC4, UPC5, UPC6,
UPC7, UPC8, UPC9, UPC10, UPC11, UPC12)
SELECT ID, UPC, SUBSTRING(UPC, 1, 1), SUBSTRING(UPC, 2, 1),
SUBSTRING(UPC, 3, 1), SUBSTRING(UPC, 4, 1), SUBSTRING(UPC, 5, 1),
SUBSTRING(UPC, 6, 1), SUBSTRING(UPC, 7, 1), SUBSTRING(UPC, 8, 1),
SUBSTRING(UPC, 9, 1), SUBSTRING(UPC, 10, 1), SUBSTRING(UPC, 11, 1),0
FROM Sfile
WHERE (LEN(UPC) = 11)

and

INSERT INTO UPCR (ID, UPC, UPC1, UPC2, UPC3, UPC4, UPC5, UPC6,
UPC7, UPC8, UPC9, UPC10, UPC11, UPC12)
SELECT ID, UPC, SUBSTRING(UPC, 12, 1), SUBSTRING(UPC, 1, 1),
SUBSTRING(UPC, 2, 1), SUBSTRING(UPC, 3, 1), SUBSTRING(UPC, 4, 1),
SUBSTRING(UPC, 5, 1), SUBSTRING(UPC, 6, 1), SUBSTRING(UPC, 7, 1),
SUBSTRING(UPC, 8, 1), SUBSTRING(UPC, 9, 1), SUBSTRING(UPC, 10, 1) ,
SUBSTRING(UPC, 11, 1)
FROM Sfile
WHERE (LEN(UPC) = 12)

and that's just the first part. I didn't know if there's an easier way
to do this.

Thanks

 

[John W. Vinson]

Well the database I'm doing it on has 11 digit UPCs, and 12. Here's
the coding:
INSERT INTO UPCR (ID, UPC, UPC1, UPC2, UPC3, UPC4, UPC5, UPC6,
UPC7, UPC8, UPC9, UPC10, UPC11, UPC12)
SELECT ID, UPC, SUBSTRING(UPC, 1, 1), SUBSTRING(UPC, 2, 1),
SUBSTRING(UPC, 3, 1), SUBSTRING(UPC, 4, 1), SUBSTRING(UPC, 5, 1),
SUBSTRING(UPC, 6, 1), SUBSTRING(UPC, 7, 1), SUBSTRING(UPC, 8, 1),
SUBSTRING(UPC, 9, 1), SUBSTRING(UPC, 10, 1), SUBSTRING(UPC, 11, 1),0
FROM Sfile
WHERE (LEN(UPC) = 11)

and

INSERT INTO UPCR (ID, UPC, UPC1, UPC2, UPC3, UPC4, UPC5, UPC6,
UPC7, UPC8, UPC9, UPC10, UPC11, UPC12)
SELECT ID, UPC, SUBSTRING(UPC, 12, 1), SUBSTRING(UPC, 1, 1),
SUBSTRING(UPC, 2, 1), SUBSTRING(UPC, 3, 1), SUBSTRING(UPC, 4, 1),
SUBSTRING(UPC, 5, 1), SUBSTRING(UPC, 6, 1), SUBSTRING(UPC, 7, 1),
SUBSTRING(UPC, 8, 1), SUBSTRING(UPC, 9, 1), SUBSTRING(UPC, 10, 1) ,
SUBSTRING(UPC, 11, 1)
FROM Sfile
WHERE (LEN(UPC) = 12)

Replace the SUBSTRING with MID and you should be in better shape. See the VBA
help for Mid(), InStr() and Len().

SUBSTRING is not a SQL operator. It's a proprietary, SQL/Server T-SQL
function. Access uses a different query engine and (for whatever historical
reasons) Microsoft did not reconcile the two different dialogs.

If you're just doing a checksum calculation, you'll probably be better off
writing a custom VBA function though - I don't see much point in adding twelve
new redundant fields to a table! I don't know your checksum algorithm but it
should be very easy to implement in code.
--


John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

Replace the SUBSTRING with MID and you should be in better shape. See theVBA
help for Mid(), InStr() and Len().

SUBSTRING is not a SQL operator. It's a proprietary, SQL/Server T-SQL
function. Access uses a different query engine and (for whatever historical
reasons) Microsoft did not reconcile the two different dialogs.

If you're just doing a checksum calculation, you'll probably be better off
writing a custom VBA function though - I don't see much point in adding twelve
new redundant fields to a table! I don't know your checksum algorithm butit
should be very easy to implement in code.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

OK I almost have it. First a stupid qustion... I want to divide a+b by
10. What would be the coding in Access? In SQL its ((a)+(b*3))%10, but
I can get it in Access to work.

Thanks

 

[John W. Vinson]

OK I almost have it. First a stupid qustion... I want to divide a+b by
10. What would be the coding in Access? In SQL its ((a)+(b*3))%10, but
I can get it in Access to work.

Thanks

The division operator in Access is <ahem> / (just like grade school
arithmatic).

You can use \ as an integer-divide operator, e.g. 10/3 = 3.333333333333333 (it
returns a Double by default); 10\3 = 3.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

The division operator in Access is <ahem> / (just like grade school
arithmatic).

You can use \ as an integer-divide operator, e.g. 10/3 = 3.333333333333333 (it
returns a Double by default); 10\3 = 3.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks, hopefully last question... How would I do this in access?
Update UPCR
Set NUPC=UPC+CAST((TSum) AS varchar(1))
where TSum = 0

In SQL that copies the UPC with the 0's. Say if it's 09999999999 it
copies it with the zero in from and adds the check digit.

 

[John W. Vinson]

Thanks, hopefully last question... How would I do this in access?
Update UPCR
Set NUPC=UPC+CAST((TSum) AS varchar(1))
where TSum = 0

In SQL that copies the UPC with the 0's. Say if it's 09999999999 it
copies it with the zero in from and adds the check digit.

NUPC = Format(UPC, "00000000000") & Format([TSum], "0")

assuming UPC is currently a Number. If it's a String with 11 bytes and you
just want to tack on the digit, just

[UPC] & Format(TSum, "0")
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

Thanks, hopefully last question... How would I do this in access?
Update UPCR
Set NUPC=UPC+CAST((TSum) AS varchar(1))
where TSum = 0

In SQL that copies the UPC with the 0's. Say if it's 09999999999 it
copies it with the zero in from and adds the check digit.

NUPC = Format(UPC, "00000000000") & Format([TSum], "0")

assuming UPC is currently a Number. If it's a String with 11 bytes and you
just want to tack on the digit, just

[UPC] & Format(TSum, "0")
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

That works, but what if I wanted to have it add what TSum-10 is. When
I do [UPC] & Format([TSum]-10,"0") I get 09999999999-2 if TSum was 8.
How can I get it to add what TSum-10 is without the -.

Thanks

 

[John W. Vinson]

That works, but what if I wanted to have it add what TSum-10 is. When
I do [UPC] & Format([TSum]-10,"0") I get 09999999999-2 if TSum was 8.
How can I get it to add what TSum-10 is without the -.

Ummm...

Reread this thread.
Bear in mind that *I do not know the algorithm*.
I do not know how you acr calculating TSum, or what result you want (should it
be 8? 2? something else?)

Sorry my telepathy is a bit on the blink today...

I'm GUESSING that you want the Abs() function to get the absolute value of the
difference, but that's a pure guess.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

That works, but what if I wanted to have it add what TSum-10 is. When
I do [UPC] & Format([TSum]-10,"0") I get 09999999999-2 if TSum was 8.
How can I get it to add what TSum-10 is without the -.

Ummm...

Reread this thread.
Bear in mind that *I do not know the algorithm*.
I do not know how you acr calculating TSum, or what result you want (should it
be 8? 2? something else?)

Sorry my telepathy is a bit on the blink today...

I'm GUESSING that you want the Abs() function to get the absolute value of the
difference, but that's a pure guess.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

TSum is figured out by this:
totalsum = ([SEven]+([SOdd]*3)) MOD 10 that gives me a number.If it's
greater than 0 I need to minus the Tsum by 10 (and that's the check
digit). Then add it to the end of the UPC. When I do this:
[UPC] & Format([TSum]-10,"0") it will take the Tsum minus 10 and adds
the number to the end of the UPC, but it also adds the "-". I need it
to take the TSum minus 10 and put the result at the end of the UPC.
Would could I do that?

Thanks

 

[John W. Vinson]

TSum is figured out by this:
totalsum = ([SEven]+([SOdd]*3)) MOD 10 that gives me a number.If it's
greater than 0 I need to minus the Tsum by 10 (and that's the check
digit). Then add it to the end of the UPC. When I do this:
[UPC] & Format([TSum]-10,"0") it will take the Tsum minus 10 and adds
the number to the end of the UPC, but it also adds the "-". I need it
to take the TSum minus 10 and put the result at the end of the UPC.
Would could I do that?

I did suggest using Abs(). To be more explicit:

[UPC] & Format(Abs([TSum] - 10), "0")

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

TSum is figured out by this:
totalsum = ([SEven]+([SOdd]*3)) MOD 10 that gives me a number.If it's
greater than 0 I need to minus the Tsum by 10 (and that's the check
digit). Then add it to the end of the UPC. When I do this:
[UPC] & Format([TSum]-10,"0") it will take the Tsum minus 10 and adds
the number to the end of the UPC, but it also adds the "-". I need it
to take the TSum minus 10 and put the result at the end of the UPC.
Would could I do that?

I did suggest using Abs(). To be more explicit:

[UPC] & Format(Abs([TSum] - 10), "0")

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Beautiful, that worked.

Thanks alot!

 

[taunt]

TSum is figured out by this:
totalsum = ([SEven]+([SOdd]*3)) MOD 10 that gives me a number.If it's
greater than 0 I need to minus the Tsum by 10 (and that's the check
digit). Then add it to the end of the UPC. When I do this:
[UPC] & Format([TSum]-10,"0") it will take the Tsum minus 10 and adds
the number to the end of the UPC, but it also adds the "-". I need it
to take the TSum minus 10 and put the result at the end of the UPC.
Would could I do that?

I did suggest using Abs(). To be more explicit:

[UPC] & Format(Abs([TSum] - 10), "0")

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Beautiful, that worked.

Thanks alot!

OK my Access skills are awful! Where I'm stuck on now I'm trying to
update the UPC field in Sfile to NUPC field in UPCR. How can I do
this.

Thanks

 

[John W. Vinson]

OK my Access skills are awful! Where I'm stuck on now I'm trying to
update the UPC field in Sfile to NUPC field in UPCR. How can I do
this.

Again...

You can see your computer.

You know the structure of your database.

I can do neither.

What are Sfile and UPCR? What tables do you have? How are they related or
structured?

I'll be glad to help, but I'll need some of your help to do so!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[taunt]

Again...

You can see your computer.

You know the structure of your database.

I can do neither.

What are Sfile and UPCR? What tables do you have? How are they related or
structured?

I'll be glad to help, but I'll need some of your help to do so!
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Sorry... Sfile and UPCR are the tables. I need it to update UPC on
Sfile to NUPC on UPCR. Based open the PID on Sfile = to RID on NUPC.

 

[John W. Vinson]

Sorry... Sfile and UPCR are the tables. I need it to update UPC on
Sfile to NUPC on UPCR. Based open the PID on Sfile = to RID on NUPC.

Well, it sounds like a very complex and long way around - I'd just update it
directly in place without using a second table - but you could create a Query
joining Sfile and UPCR on the appropriate joining field (which I don't know;
it would need to be the Primary Key of the table you're updating, or at least
have a unique Index). Change the query to an Update Query and put

[RID]

in brackets on the PID field and run the query.

As I said, this is going all the way around Robin's barn to do something
simple:

UPDATE Sfile
SET UPC = [UPC] & Format(<some complex expression>, "0")

would do the same thing in one pass.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com