Binary string into ASCII converstion? (1 Viewer)

hardrock

Registered User.
Local time
Today, 16:19
Joined
Apr 5, 2007
Messages
166
Hi all, does anyone have a function that will convert a binary string into ASCII using vba or sql. I'm not sure if this can be done in Access as i can't find any resource to do it on google. Any help appreciated!
 

Ron_dK

Cool bop aficionado
Local time
Today, 17:19
Joined
Sep 5, 2002
Messages
2,141
I haven't tested this , but maybe something like this :

Code:
Dim bin As String = "1000001" [COLOR="SeaGreen"]'Binary string (A's ASCII Code 65)[/COLOR]
Dim decimalAscIIValue As Integer
decimalAscIIValue = Convert.ToInt32(bin, 2) [COLOR="SeaGreen"]'First convert Binary to Decimal Intger[/COLOR]
Dim AscIIChar As String = Chr(decimalAscIIValue) [COLOR="SeaGreen"]'Then convert Decimal to ASCII Character[/COLOR]
TextBox.Text = AscIIChar[COLOR="SeaGreen"] 'AscIIChar =A. Display it in the multi-line textbox [/COLOR]

Hth
 

hardrock

Registered User.
Local time
Today, 16:19
Joined
Apr 5, 2007
Messages
166
Thankyou so much for the responses guys, i have tried the code samples as suggested, but i am still struggling to get this function to work.

We have a bit of script code that does work, but i am trying to convert it to work with Access. I think the CAST function is where everything is going wrong? Heres the code sniplet

LET SqlString ("SELECT REPLACE(CAST(t_text AS CHAR(1024)),CHAR(10),'<BR>') TextString "+
"FROM texttable "+
"WHERE text=" 1234"

I hope someone can help me because, i really can't find any solution to this problem on the internet. Cheers!
 

LPurvis

AWF VIP
Local time
Today, 16:19
Joined
Jun 16, 2008
Messages
1,269
Is this just data in a binary type field in your DB that you're talking about it?
What does the data in it look like before and after?
What are you converting it for - and where in Access?
 

datAdrenaline

AWF VIP
Local time
Today, 11:19
Joined
Jun 23, 2008
Messages
697
I have a function in the download found by Clicking Here that will help... the function is called BinToDec(), here is the code.

Code:
Public Function BinToDec(ByVal strBinary As String, _
                         Optional blSigned As Boolean = True) As Currency
'Converts the passed binary string into a decimal value.  blSigned is defaulted to true, which
'means the returned result will be "signed" using the left most bit of a 2, or 4 byte string
'as the sign.  Note: for signed systems, it is imperative to know the byte size you are
'working with, in MSAccess negatives values must be in at least 2 bytes,
'For the scope of this code, there is a 4 byte maximum.
'
'Example Usage:
'-----------------
'BinToDec("1100") -> 12
'BinToDec("1100,True") -> 12
'BinToDec("10001100",True) -> 140
'BinToDec("11111111 11111111") -> 65535
'BinToDec("01111111 11111111,True") -> 32767
'BinToDec("11111111 11111111",True) -> -1
'BinToDex("10000000 00000000",True) -> -32768
'--------------------------------------------------------------------------------
    Dim x As Integer  'An index
    Dim intLength As Integer  'the length of the passed string passed
    Dim curValueOffset As Currency  'The value of the offset (used for negatives)
    Dim curValue As Currency 'The numeric value be returned
    Dim intPadSize As Integer
    Dim strTemp As String 'A temp string
    'Strip invalid characters and find the length of the passed string
    For x = 1 To Len(strBinary)
        If InStr("01", Mid(strBinary, x, 1)) > 0 Then
            strTemp = strTemp & Mid(strBinary, x, 1)
        End If
    Next x
    strBinary = strTemp
 
    'Test for too long of binary value, plus pad with "0"'s if less that 32
    If Len(strBinary) > 32 Then
        MsgBox "Overflow", vbCritical, "Error"
        Exit Function
    Else
        If Len(strBinary) > 16 Then
            intPadSize = 32
        Else
            intPadSize = 16
        End If
    End If
    strBinary = String(intPadSize - Len(strBinary), "0") & strBinary
    intLength = Len(strBinary)
 
    'Loop through the right most bits, saveing the last one for later
    'Note the loop counter increments up, but the character evaluation is
    'from the right to the left, the the left most bit is not checked in
    'this loop
    For x = 0 To intLength - 2
        If Mid(strBinary, intLength - x, 1) = "1" Then
            curValue = curValue + 2 ^ x
        End If
    Next x
 
    'Evaluate the leftmost bit and return the result
    If Left(strBinary, 1) = "1" Then
        If blSigned = True Then
            BinToDec = curValue - 2 ^ (intLength - 1)
        Else
            BinToDec = curValue + 2 ^ (intLength - 1)
        End If
    Else
        BinToDec = curValue
    End If
 
End Function

Then once you have the decimal value, you can use the Chr() function ... {or ChrW()}

? Chr(BinToDec("1000001"))
A
 
Last edited:

Banana

split with a cherry atop.
Local time
Today, 09:19
Joined
Sep 1, 2005
Messages
6,318
Just throwing out another approach-

Put the binary stream into a byte array, and do a StrConv()

Code:
Dim i As Long
Dim x() As Byte
Dim str As String
' Convert string to ANSI byte array
x = StrConv("abcdef", vbFromUnicode)
For i = 0 To UBound(x)
   Debug.Print x(i);
Next
Debug.Print
' Convert back to a string
str = StrConv(x(), vbUnicode)
Debug.Print str

Props to David Ireland
 

hardrock

Registered User.
Local time
Today, 16:19
Joined
Apr 5, 2007
Messages
166
Thanks guys, but unfortunately still no luck!

Attached is a screenshot of the suspect binary column (t_text) within the table when opened up in Access 2007 and 2003. As can be seen, It appears Access can't interpret the string correct, and thats probably where its going wrong. A mate of mine managed to get the code below to work in Excel 2007, linking direct to the table in SQL, but neither of us can get it to work in Access!

LET SqlString ("SELECT REPLACE(CAST(t_text AS CHAR(1024)),CHAR(10),'<BR>') TextString "+
"FROM dbo.texttable "+
"WHERE t_ctxt =" 961"
 

Attachments

  • screenshot.JPG
    screenshot.JPG
    57.3 KB · Views: 587

WayneRyan

AWF VIP
Local time
Today, 16:19
Joined
Nov 19, 2002
Messages
7,122
HardRock,

I take it that you have a SQL Server table that has a Text field in it.

You're gonna have to use the ReadText function to grab the data in
small sections. I'm guessing that it is text ... you aren't converting
the data, you're just extracting it.

If it was an image data type, then you'd need to convert.

Neither SQL Server nor Access are going to be of much help here.

Wayne
 

Users who are viewing this thread

Top Bottom