colinasad
asked on
Is it possible to construct a full EAN 13 barcode string in a SQL SELECT query?
I am developing an Access "project" (.adp) as a front-end to a SQL Server database.
My client has his own EAN 13 barcode prefix and each stock item has its own unique number. I have been able to produce VBA code to pack put the stock number with leading zeroes, join it to the prefix and calculate the 13th checksum digit.
That all works OK for working within Access, but I am now using the SQL Server data as an ODBC source and would like to offer the full 13 digit barcode as a column from a new "View".
Can anyone suggest how I could create the full barcode in a SQL SELECT statement?
Many thanks. Colin.
My client has his own EAN 13 barcode prefix and each stock item has its own unique number. I have been able to produce VBA code to pack put the stock number with leading zeroes, join it to the prefix and calculate the 13th checksum digit.
That all works OK for working within Access, but I am now using the SQL Server data as an ODBC source and would like to offer the full 13 digit barcode as a column from a new "View".
Can anyone suggest how I could create the full barcode in a SQL SELECT statement?
Many thanks. Colin.
You may want to try this VBA encoder; it contains the ability to encode EAN barcodes. Though you must also use a barcode font.
http://www.idautomation.com/fonts/tools/vba/
Hope this helps.
http://www.idautomation.com/fonts/tools/vba/
Hope this helps.
ASKER
Hello again, cyberkiwi. Thanks for the prompt response.
I have attached my VBA procedure for building a full 13 character barcode string.
I wouldn't know how to start to do this with SQL. I would be able to construct the first 12 digits OK but wouldn't know how to do the checksum calculation.
Regards. Colin.
I have attached my VBA procedure for building a full 13 character barcode string.
I wouldn't know how to start to do this with SQL. I would be able to construct the first 12 digits OK but wouldn't know how to do the checksum calculation.
Regards. Colin.
Public Function genBuildFullBarcode(strPrefix As String, lngNumber As Long, blnSeparate As Boolean) As String
' Build a full 13 digit barcode from a prefix and unique number - checksum digit is calculated
' strPrefix = Barcode prefix digits
' lngNumber = Item's unique number
' blnSeparate = Do we want to show gaps between main components ie ppppppp-nnnnn-c
Dim intEvenTot As Integer
Dim intOddTot As Integer
Dim intCounter As Integer
Dim strBarcode As String
intEvenTot = 0
intOddTot = 0
strBarcode = ""
' Pack out the unique number with leading zeroes and make it 5 digits long
strBarcode = Trim(Str(lngNumber))
strBarcode = Right("00000" & strBarcode, 5)
' Concatenate the unique number to the prefix (already 7 digits long)
strBarcode = strPrefix & strBarcode
' Now calculate the 13th checksum digit
If Len(strBarcode) = 12 Then
For intCounter = 1 To 12
If (intCounter Mod 2) = 0 Then
intEvenTot = intEvenTot + Asc(Mid(strBarcode, intCounter, 1)) - Asc("0")
Else
intOddTot = intOddTot + Asc(Mid(strBarcode, intCounter, 1)) - Asc("0")
End If
Next intCounter
intEvenTot = (intEvenTot * 3) + intOddTot
intEvenTot = (intEvenTot Mod 10)
If intEvenTot > 0 Then
intEvenTot = 10 - intEvenTot
End If
If blnSeparate Then
' Add a separator char to show how string is composed
strBarcode = Trim(Str(lngNumber))
strBarcode = Right("00000" & strBarcode, 5)
strBarcode = strPrefix & "-" & strBarcode & "-" & Trim(Str(intEvenTot))
Else
strBarcode = strBarcode & Trim(Str(intEvenTot))
End If
End If
genBuildFullBarcode = strBarcode
End Function ' genBuildFullBarcode
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks, cyberkiwi, that worked fine.
I'm ashamed to say that I have not created any SQL Functions before. Because my application is in Access, nearly all of my programming is done in VBA and I have limited by SQL input to Views and Stored Procedures.
You have opened a new door for me.
Just before I award the well-earned points, can I ask if you painstakingly edited my VBA code by hand (replacing the comment prefix characters, changing "dim" to "declare", adding in "set" etc.) or is there a "VBA to SQL" convertor that does most of this?
Many thanks. Colin.
I'm ashamed to say that I have not created any SQL Functions before. Because my application is in Access, nearly all of my programming is done in VBA and I have limited by SQL input to Views and Stored Procedures.
You have opened a new door for me.
Just before I award the well-earned points, can I ask if you painstakingly edited my VBA code by hand (replacing the comment prefix characters, changing "dim" to "declare", adding in "set" etc.) or is there a "VBA to SQL" convertor that does most of this?
Many thanks. Colin.
By hand, sir! :)
It was a hack job line for line.
If you expressed just the requirements, you may see a different approach, but I think this will do.
Have a good day.
Regards
It was a hack job line for line.
If you expressed just the requirements, you may see a different approach, but I think this will do.
Have a good day.
Regards
ASKER
Many thanks, cyberkiwi
Your conversion of my Access VBA procedure to a SQL Server SQL function worked a treat.
Your conversion of my Access VBA procedure to a SQL Server SQL function worked a treat.
Need the VBA code though.