Created
November 12, 2019 12:42
Revisions
-
JamborJan created this gist
Nov 12, 2019 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,72 @@ /*# # # # # # # # # # # # # # # # # # # # # # # # # # # # 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])<>""