UPC check digit?

  • Thread starter Thread starter taunt
  • Start date Start date
T

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
 
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
Click to expand...

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
 
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
Click to expand...


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
 
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)
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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.
 
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.
Click to expand...

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
 
Thanks, hopefully last question... How would I do this in access?
Update UPCR
Set NUPC=UPC+CAST((TSum) AS varchar(1))
where TSum = 0
Click to expand...
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.
Click to expand...

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
Click to expand...

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
 
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 -.
Click to expand...

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
 
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 -.
Click to expand...

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
Click to expand...

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
 
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?
Click to expand...

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
 
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?
Click to expand...

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
Click to expand...

Beautiful, that worked.

Thanks alot!
 
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?
Click to expand...
Click to expand...
I did suggest using Abs(). To be more explicit:
Click to expand...
[UPC] & Format(Abs([TSum] - 10), "0")
--
Click to expand...

             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
Click to expand...

Beautiful, that worked.

Thanks alot!
Click to expand...

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
 
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.
Click to expand...

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
 
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
Click to expand...

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.
 
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.
Click to expand...

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
 
Back
Top