Formatted characters show as blocks

  • Thread starter Thread starter sck10
  • Start date Start date
S

sck10

Hello,

I am using SS2K. We are storing documents from html and word files into a
varchar(4000) field. The problem that I am having is that we have document
containing single quotes, left and right double quotes, comma's or ellipses
that are viewable in the table when using SQL Enterprise Manger, but only
show up as blocks when using MS Access.

Is there a way to use an update query to replace these characters with
characters that Access will recognize?
 
Hi,

First check that the font you're using in Access contains the relevant
glyphs. Normally, the characters displays OK in Word they should display
OK in Access (unless you're using Office 97, where Word can use Unicode
but Access can't).

After that, maybe your DBA can fix it for you.

Otherwise, here's a VBA function I've used for this sort of job. It
should work if called in an update query.

Public Function Transliterate(ByVal Target As Variant, _
ByVal FindList As String, _
ByVal ReplaceList As String) As Variant

'Replaces each character in FindList with the
'corresponding character in ReplaceList.

Dim lngPos As Long
Dim Char As String
Dim lngIndex As Long
Dim S As String

If IsNull(Target) Or Len(CStr(Nz(Target, vbNullStr))) = 0 Then
Transliterate = Null
Exit Function
End If
If Len(FindList) <> Len(ReplaceList) Then
Transliterate = CVErr(9999) 'crude, but shows up as an
'error in a query.
'alternatively perhaps use a MessageBox.
Exit Function
End If

S = CStr(Target)
For lngPos = 1 To Len(S)
Char = Mid(S, lngPos, 1)
lngIndex = InStr(FindList, Char)
If lngIndex > 0 Then 'Char needs to be replaced
'next line uses the uncommon Mid() statement
'as well as the Mid() function
Mid(S, lngPos, 1) = Mid(ReplaceList, lngIndex, 1)
End If
Next
Transliterate = S
End Function
 
Back
Top