UPC check digit?

Post by 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

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

2010-11-03 16:29:38 UTC

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

2010-11-03 16:52:26 UTC

Post by taunt
Well the database I'm doing it on has 11 digit UPCs, and 12. Here's
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.

taunt

2010-11-03 22:14:05 UTC

On Nov 3, 10:52 am, John W. Vinson

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

2010-11-03 23:41:21 UTC

Post by taunt
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

taunt

2010-11-04 16:19:49 UTC

Post by taunt
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]
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

2010-11-04 17:43:22 UTC

Post by taunt
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

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")

taunt

2010-11-04 18:49:51 UTC

On Nov 4, 11:43 am, John W. Vinson

Post by taunt
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

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

2010-11-04 19:21:43 UTC

Post by 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 -.

taunt

2010-11-04 19:48:18 UTC

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

2010-11-04 20:11:50 UTC

Post by taunt
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
[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")

taunt

2010-11-04 20:31:24 UTC

[UPC] & Format(Abs([TSum] - 10), "0")
--
John W. Vinson [MVP]
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

2010-11-04 21:47:13 UTC

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

2010-11-04 22:04:45 UTC

Post by taunt
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.

taunt

2010-11-04 22:41:18 UTC

Post by taunt
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]
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

2010-11-04 23:48:52 UTC