We developed a MS Access which creates invoices and has a Code 128 barcode on it. When an invoice is created it enters a value into a field named Barcode which is the data used by the Code 128 font to create the barcode.
The data stored in the field typically looks like this: -
Í_D#nEÇp È8-Î
The number printed below the bar code for the above is: -
63360378379980000000008
The code used to create the barcode is: -
Public Function M_Create_Code_128(Barcode As String)
'This function is governed by the GNU Lesser General Public License (GNU LGPL)
'V 2.0.0
'Parameters : a string
'Return : * a string which give the bar code when it is dispayed with CODE128.TTF font
' * an empty string if the supplied parameter is no good
Dim i%, checksum&, mini%, dummy%, tableB As Boolean
M_Create_Code_128 = ""
If Len(Barcode) > 0 Then
'Check for valid characters
For i% = 1 To Len(Barcode)
Select Case Asc(Mid$(Barcode, i%, 1))
Case 32 To 126, 203
Case Else
i% = 0
Exit For
End Select
Next
'Calculation of the code string with optimized use of tables B and C
M_Create_Code_128 = ""
tableB = True
If i% > 0 Then
i% = 1 'i% devient l'index sur la chaine / i% become the string index
Do While i% <= Len(Barcode)
If tableB Then
'See if interesting to switch to table C
'yes for 4 digits at start or end, else if 6 digits
mini% = IIf(i% = 1 Or i% + 3 = Len(Barcode), 4, 6)
GoSub testnum
If mini% < 0 Then 'Choice of table C
If i% = 1 Then 'Starting with table C
M_Create_Code_128 = Chr$(205)
Else 'Switch to table C
M_Create_Code_128 = M_Create_Code_128 & Chr$(199)
End If
tableB = False
Else
If i% = 1 Then M_Create_Code_128 = Chr$(204) 'Starting with table B
End If
End If
If Not tableB Then
'We are on table C, try to process 2 digits
mini% = 2
GoSub testnum
If mini% < 0 Then 'OK for 2 digits, process it
dummy% = Val(Mid$(Barcode, i%, 2))
dummy% = IIf(dummy% < 95, dummy% + 32, dummy% + 100)
M_Create_Code_128 = M_Create_Code_128 & Chr$(dummy%)
i% = i% + 2
Else 'We haven't 2 digits, switch to table B
M_Create_Code_128 = M_Create_Code_128 & Chr$(200)
tableB = True
End If
End If
If tableB Then
'Process 1 digit with table B
M_Create_Code_128 = M_Create_Code_128 & Mid$(Barcode, i%, 1)
i% = i% + 1
End If
Loop
'Calculation of the checksum
For i% = 1 To Len(M_Create_Code_128)
dummy% = Asc(Mid$(M_Create_Code_128, i%, 1))
dummy% = IIf(dummy% < 127, dummy% - 32, dummy% - 100)
If i% = 1 Then checksum& = dummy%
checksum& = (checksum& + (i% - 1) * dummy%) Mod 103
Next
'Calculation of the checksum ASCII code
checksum& = IIf(checksum& < 95, checksum& + 32, checksum& + 100)
'Add the checksum and the STOP
M_Create_Code_128 = M_Create_Code_128 & Chr$(checksum&) & Chr$(206)
End If
End If
Exit Function
testnum:
'if the mini% characters from i% are numeric, then mini%=0
mini% = mini% - 1
If i% + mini% <= Len(Barcode) Then
Do While mini% >= 0
If Asc(Mid$(Barcode, i% + mini%, 1)) < 48 Or Asc(Mid$(Barcode, i% + mini%, 1)) > 57 Then Exit Do
mini% = mini% - 1
Loop
End If
Return
End Function
We have now created a web app to replace the MS Access version which has an MS SQL backend db and we now require an equivalent to the above function in SQL.
I was forwarded a link from this forum which looked very positive: -
(BARCODE 128 Encoding - SQL Server Forums TOPIC_ID=70901)
Scrolling down to matthosking code dated 2013-07-22 : 01:52:45 in the link, I copied the function: -
CREATE FUNCTION dbo.CreateBarcodeCode128
I Entered a @Barcode of 63360378378280000000004 and it produced this: -
ÌÇ_D#nErpÂÂÂÂ$]Î
If I enter the came barcode into the VB used in the original MS Access db I get this: -
Í_D#nErp È4aÎ
Something is different, but I am not sure what?