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!

I haven't tested this , but maybe something like this :

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

Hth

Check the following link

http://www.vb-helper.com/howto_binary_to_text.html

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!

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?

I have a function in the download found by Clicking Here (http://www.utteraccess.com/forums/showflat.php?Number=1408366&fpart=all#Post1408366) that will help... the function is called BinToDec(), here is the 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

Just throwing out another approach-

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

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 (http://www.di-mgt.com.au/crypto.html)

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"

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