Skip to content

Instantly share code, notes, and snippets.

@JamborJan

JamborJan/MS Access SQL SSCC.sql

Created November 12, 2019 12:42
Show Gist options
  • Save JamborJan/0afc1f79ebaf9043ba9d2d631ad1261d to your computer and use it in GitHub Desktop.
Save JamborJan/0afc1f79ebaf9043ba9d2d631ad1261d to your computer and use it in GitHub Desktop.
Download ZIP
Prüfziffernberechnung SSCC-Nr. im Code EAN 128 in MS Access mit SQL ohne Verwendung VBA
Raw
MS Access SQL SSCC.sql
/*# # # # # # # # # # # # # # # # # # # # # # # # # #
#
# Content: Prüfziffernberechnung SSCC-Nr. im Code EAN 128
# in MS Access mit SQL ohne Verwendung VBA. Spezifikation gemäss
# http://www.kbconsult.ch/PdF%20File/Barcode_Prufzifferberechnung.pdf
#
# Author: Jan Jambor, XWare GmbH
# Author URI: https://xwr.ch
# Date: 12.11.2019
#
# # # # # # # # # # # # # # # # # # # # # # # # # #*/
/* Kurzversion */
SELECT
BT.[HUIDENT],
BT.[HUIDENT]&RIGHT(CStr(10-((3*CINT(MID(BT.[HUIDENT],1,1))+1*CINT(MID(BT.[HUIDENT],2,1))+3*CINT(MID(BT.[HUIDENT],3,1))+1*CINT(MID(BT.[HUIDENT],4,1))+3*CINT(MID(BT.[HUIDENT],5,1))+1*CINT(MID(BT.[HUIDENT],6,1))+3*CINT(MID(BT.[HUIDENT],7,1))+1*CINT(MID(BT.[HUIDENT],8,1))+3*CINT(MID(BT.[HUIDENT],9,1))+1*CINT(MID(BT.[HUIDENT],10,1))+3*CINT(MID(BT.[HUIDENT],11,1))+1*CINT(MID(BT.[HUIDENT],12,1))+3*CINT(MID(BT.[HUIDENT],13,1))+1*CINT(MID(BT.[HUIDENT],14,1))+3*CINT(MID(BT.[HUIDENT],15,1))+1*CINT(MID(BT.[HUIDENT],16,1))+3*CINT(MID(BT.[HUIDENT],17,1))) MOD 10)),1) AS SSCC,
FROM
[Bestand EWM Temp] BT
/* Langversion mit Herleitung */
SELECT
BT.[HUIDENT],
BT.[HUIDENT]&RIGHT(CStr(10-((3*CINT(MID(BT.[HUIDENT],1,1))+1*CINT(MID(BT.[HUIDENT],2,1))+3*CINT(MID(BT.[HUIDENT],3,1))+1*CINT(MID(BT.[HUIDENT],4,1))+3*CINT(MID(BT.[HUIDENT],5,1))+1*CINT(MID(BT.[HUIDENT],6,1))+3*CINT(MID(BT.[HUIDENT],7,1))+1*CINT(MID(BT.[HUIDENT],8,1))+3*CINT(MID(BT.[HUIDENT],9,1))+1*CINT(MID(BT.[HUIDENT],10,1))+3*CINT(MID(BT.[HUIDENT],11,1))+1*CINT(MID(BT.[HUIDENT],12,1))+3*CINT(MID(BT.[HUIDENT],13,1))+1*CINT(MID(BT.[HUIDENT],14,1))+3*CINT(MID(BT.[HUIDENT],15,1))+1*CINT(MID(BT.[HUIDENT],16,1))+3*CINT(MID(BT.[HUIDENT],17,1))) MOD 10)),1) AS SSCC,
LEN(BT.[HUIDENT]) AS [LÄNGE],
MID(BT.[HUIDENT],1,1) AS POS18,
MID(BT.[HUIDENT],2,1) AS POS17,
MID(BT.[HUIDENT],3,1) AS POS16,
MID(BT.[HUIDENT],4,1) AS POS15,
MID(BT.[HUIDENT],5,1) AS POS14,
MID(BT.[HUIDENT],6,1) AS POS13,
MID(BT.[HUIDENT],7,1) AS POS12,
MID(BT.[HUIDENT],8,1) AS POS11,
MID(BT.[HUIDENT],9,1) AS POS10,
MID(BT.[HUIDENT],10,1) AS POS09,
MID(BT.[HUIDENT],11,1) AS POS08,
MID(BT.[HUIDENT],12,1) AS POS07,
MID(BT.[HUIDENT],13,1) AS POS06,
MID(BT.[HUIDENT],14,1) AS POS05,
MID(BT.[HUIDENT],15,1) AS POS04,
MID(BT.[HUIDENT],16,1) AS POS03,
MID(BT.[HUIDENT],17,1) AS POS02,
RIGHT(CStr(10-((3*CINT(MID(BT.[HUIDENT],1,1))+1*CINT(MID(BT.[HUIDENT],2,1))+3*CINT(MID(BT.[HUIDENT],3,1))+1*CINT(MID(BT.[HUIDENT],4,1))+3*CINT(MID(BT.[HUIDENT],5,1))+1*CINT(MID(BT.[HUIDENT],6,1))+3*CINT(MID(BT.[HUIDENT],7,1))+1*CINT(MID(BT.[HUIDENT],8,1))+3*CINT(MID(BT.[HUIDENT],9,1))+1*CINT(MID(BT.[HUIDENT],10,1))+3*CINT(MID(BT.[HUIDENT],11,1))+1*CINT(MID(BT.[HUIDENT],12,1))+3*CINT(MID(BT.[HUIDENT],13,1))+1*CINT(MID(BT.[HUIDENT],14,1))+3*CINT(MID(BT.[HUIDENT],15,1))+1*CINT(MID(BT.[HUIDENT],16,1))+3*CINT(MID(BT.[HUIDENT],17,1))) MOD 10)),1) AS POS01,
3*CINT(MID(BT.[HUIDENT],1,1)) AS CALC18,
1*CINT(MID(BT.[HUIDENT],2,1)) AS CALC17,
3*CINT(MID(BT.[HUIDENT],3,1)) AS CALC16,
1*CINT(MID(BT.[HUIDENT],4,1)) AS CALC15,
3*CINT(MID(BT.[HUIDENT],5,1)) AS CALC14,
1*CINT(MID(BT.[HUIDENT],6,1)) AS CALC13,
3*CINT(MID(BT.[HUIDENT],7,1)) AS CALC12,
1*CINT(MID(BT.[HUIDENT],8,1)) AS CALC11,
3*CINT(MID(BT.[HUIDENT],9,1)) AS CALC10,
1*CINT(MID(BT.[HUIDENT],10,1)) AS CALC09,
3*CINT(MID(BT.[HUIDENT],11,1)) AS CALC08,
1*CINT(MID(BT.[HUIDENT],12,1)) AS CALC07,
3*CINT(MID(BT.[HUIDENT],13,1)) AS CALC06,
1*CINT(MID(BT.[HUIDENT],14,1)) AS CALC05,
3*CINT(MID(BT.[HUIDENT],15,1)) AS CALC04,
1*CINT(MID(BT.[HUIDENT],16,1)) AS CALC03,
3*CINT(MID(BT.[HUIDENT],17,1)) AS CALC02,
"" AS CALC01,
3*CINT(MID(BT.[HUIDENT],1,1))+1*CINT(MID(BT.[HUIDENT],2,1))+3*CINT(MID(BT.[HUIDENT],3,1))+1*CINT(MID(BT.[HUIDENT],4,1))+3*CINT(MID(BT.[HUIDENT],5,1))+1*CINT(MID(BT.[HUIDENT],6,1))+3*CINT(MID(BT.[HUIDENT],7,1))+1*CINT(MID(BT.[HUIDENT],8,1))+3*CINT(MID(BT.[HUIDENT],9,1))+1*CINT(MID(BT.[HUIDENT],10,1))+3*CINT(MID(BT.[HUIDENT],11,1))+1*CINT(MID(BT.[HUIDENT],12,1))+3*CINT(MID(BT.[HUIDENT],13,1))+1*CINT(MID(BT.[HUIDENT],14,1))+3*CINT(MID(BT.[HUIDENT],15,1))+1*CINT(MID(BT.[HUIDENT],16,1))+3*CINT(MID(BT.[HUIDENT],17,1)) AS [SUMME],
(3*CINT(MID(BT.[HUIDENT],1,1))+1*CINT(MID(BT.[HUIDENT],2,1))+3*CINT(MID(BT.[HUIDENT],3,1))+1*CINT(MID(BT.[HUIDENT],4,1))+3*CINT(MID(BT.[HUIDENT],5,1))+1*CINT(MID(BT.[HUIDENT],6,1))+3*CINT(MID(BT.[HUIDENT],7,1))+1*CINT(MID(BT.[HUIDENT],8,1))+3*CINT(MID(BT.[HUIDENT],9,1))+1*CINT(MID(BT.[HUIDENT],10,1))+3*CINT(MID(BT.[HUIDENT],11,1))+1*CINT(MID(BT.[HUIDENT],12,1))+3*CINT(MID(BT.[HUIDENT],13,1))+1*CINT(MID(BT.[HUIDENT],14,1))+3*CINT(MID(BT.[HUIDENT],15,1))+1*CINT(MID(BT.[HUIDENT],16,1))+3*CINT(MID(BT.[HUIDENT],17,1))) MOD 10 AS [MOD10]
FROM
[Bestand EWM Temp] BT
/* Beispiel Update */
UPDATE [Bestand EWM Temp] BT
SET
BT.[HUIDENT]=BT.[HUIDENT]&RIGHT(CStr(10-((3*CINT(MID(BT.[HUIDENT],1,1))+1*CINT(MID(BT.[HUIDENT],2,1))+3*CINT(MID(BT.[HUIDENT],3,1))+1*CINT(MID(BT.[HUIDENT],4,1))+3*CINT(MID(BT.[HUIDENT],5,1))+1*CINT(MID(BT.[HUIDENT],6,1))+3*CINT(MID(BT.[HUIDENT],7,1))+1*CINT(MID(BT.[HUIDENT],8,1))+3*CINT(MID(BT.[HUIDENT],9,1))+1*CINT(MID(BT.[HUIDENT],10,1))+3*CINT(MID(BT.[HUIDENT],11,1))+1*CINT(MID(BT.[HUIDENT],12,1))+3*CINT(MID(BT.[HUIDENT],13,1))+1*CINT(MID(BT.[HUIDENT],14,1))+3*CINT(MID(BT.[HUIDENT],15,1))+1*CINT(MID(BT.[HUIDENT],16,1))+3*CINT(MID(BT.[HUIDENT],17,1))) MOD 10)),1)
WHERE
LEN(BT.[HUIDENT])<18
AND IIf(IsNull(BT.[HUIDENT]),"",BT.[HUIDENT])<>""
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment