Querying external tables with binary fields

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

When querying an external Sybase table, some fields are
not displayed properly. When viewing the table design
from Access, the fields show a data type as binary. I
have used Excel to query the same external table and it
returns a numeric value. Any idea why or what can be done?
Thanks,
Pete
 
You will need to convert the binary to text. The basic conversion seems to
be:

rtf = StrConv(.Fields("Name of your table with the binary field"),
vbUnicode)

This will give text and a lot of 'garbage' that needs to be stripped off.
I have tried the following with some limited success. None of this is by
any means perfect and I am still trying to get the whole process working
properly, but it is a start and maybe you might be able to finalise it for
yourself.

Function ExtractTextFromRTF(sRTF As String) As String

Dim s As String, p As Integer

' find last }
p = InStrRev(sRTF, "}")

' trim stuff from end
s = Left(sRTF, p - 1)

' find previous }
p = InStrRev(s, "}")

' if there is one then trim everything before it
If p Then s = Mid(s, p + 1)

' find first space
p = InStr(s, " ")

' trim everything up to and including it
s = Mid(s, p + 1)

' replace \par directives by CR/LF
s = Replace(s, "\par", vbCrLf)

ExtractTextFromRTF = s

rtfnotes = s

End Function


Good luck
 
Back
Top