Well, today I needed to process some data for upload to a web page
and it needed higher ASCII characters encoded as HTML entities.
So, I wrote a function to do the job, which works with a table of
the entities.
The table and the function, along with sample data and a sample
query using the function, can be downloaded from:
A couple of comments:
1. because Access is not case sensitive but the HTML entities *are*
case sensitive, there are a couple of little kludges:
a. the data table has a checkoff field to indicate if an entity
is an upper-case version of an entity. Examples would be á
and &Aaccute.
b. the function that does the replacing compares the character
being encoded to the upper case version of the same character. If
it's the same, it capitalizes the 2nd character of the entity
encoding.
So, what happens in the function is that it looks up only the lower
case version, then if the character being encoded is upper case, it
alters the entity to be uppercase. Example:
É [capital e acute]
will look up the entity definition:
é
Then the upper case version of the character being tested is
compared to the character itself:
If Asc(UCase(strCh ar)) = Asc(strChar) Then
and if it's equal, it converts the retrieved entity, "é", to
"É".
The code for the function is after my signature below, and uses
Trevor Best's tLookup function in a version based on his old
version (he's since rewritten it significantly).
Note also that the code works either as a function or with a ByRef
variable passed to it.
[confidential to Steve J.: yes, I used a static variable -- I'm
changing my mind on this]
Commentary, suggestions and improvements welcome.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Public Function HTMLEntityRepla ce(varInput As Variant) As Variant
Static db As DAO.Database
Dim lngLen As Long
Dim i As Long
Dim lngOutputCounte r As Long
Dim strChar As String
Dim strEntity As String
Dim lngLenEntity As Long
Dim strOutput As String
lngLen = Len(Nz(varInput ))
If lngLen = 0 Or IsNull(varInput ) Then GoTo exitRoutine
If db Is Nothing Then Set db = CurrentDb()
strOutput = varInput
For i = 1 To lngLen
lngOutputCounte r = lngOutputCounte r + 1
strChar = Mid(varInput, i, 1)
If Asc(strChar) > 128 Then
strEntity = Nz(tLookup("HTM LEntity", "tblHTMLEntitie s", _
"[Letter]='" & strChar & "' AND [UCase]=False", db), _
vbNullString)
lngLenEntity = Len(strEntity)
If lngLenEntity > 0 Then
If Asc(UCase(strCh ar)) = Asc(strChar) Then
strEntity = "&" & StrConv(Mid(str Entity, 2), _
vbProperCase)
End If
strOutput = Left(strOutput, lngOutputCounte r - 1) _
& strEntity & Mid(strOutput, lngOutputCounte r + 1)
lngOutputCounte r = lngOutputCounte r + lngLenEntity - 1
End If
End If
Next i
varInput = strOutput
HTMLEntityRepla ce = strOutput
exitRoutine:
Exit Function
End Function
and it needed higher ASCII characters encoded as HTML entities.
So, I wrote a function to do the job, which works with a table of
the entities.
The table and the function, along with sample data and a sample
query using the function, can be downloaded from:
A couple of comments:
1. because Access is not case sensitive but the HTML entities *are*
case sensitive, there are a couple of little kludges:
a. the data table has a checkoff field to indicate if an entity
is an upper-case version of an entity. Examples would be á
and &Aaccute.
b. the function that does the replacing compares the character
being encoded to the upper case version of the same character. If
it's the same, it capitalizes the 2nd character of the entity
encoding.
So, what happens in the function is that it looks up only the lower
case version, then if the character being encoded is upper case, it
alters the entity to be uppercase. Example:
É [capital e acute]
will look up the entity definition:
é
Then the upper case version of the character being tested is
compared to the character itself:
If Asc(UCase(strCh ar)) = Asc(strChar) Then
and if it's equal, it converts the retrieved entity, "é", to
"É".
The code for the function is after my signature below, and uses
Trevor Best's tLookup function in a version based on his old
version (he's since rewritten it significantly).
Note also that the code works either as a function or with a ByRef
variable passed to it.
[confidential to Steve J.: yes, I used a static variable -- I'm
changing my mind on this]
Commentary, suggestions and improvements welcome.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Public Function HTMLEntityRepla ce(varInput As Variant) As Variant
Static db As DAO.Database
Dim lngLen As Long
Dim i As Long
Dim lngOutputCounte r As Long
Dim strChar As String
Dim strEntity As String
Dim lngLenEntity As Long
Dim strOutput As String
lngLen = Len(Nz(varInput ))
If lngLen = 0 Or IsNull(varInput ) Then GoTo exitRoutine
If db Is Nothing Then Set db = CurrentDb()
strOutput = varInput
For i = 1 To lngLen
lngOutputCounte r = lngOutputCounte r + 1
strChar = Mid(varInput, i, 1)
If Asc(strChar) > 128 Then
strEntity = Nz(tLookup("HTM LEntity", "tblHTMLEntitie s", _
"[Letter]='" & strChar & "' AND [UCase]=False", db), _
vbNullString)
lngLenEntity = Len(strEntity)
If lngLenEntity > 0 Then
If Asc(UCase(strCh ar)) = Asc(strChar) Then
strEntity = "&" & StrConv(Mid(str Entity, 2), _
vbProperCase)
End If
strOutput = Left(strOutput, lngOutputCounte r - 1) _
& strEntity & Mid(strOutput, lngOutputCounte r + 1)
lngOutputCounte r = lngOutputCounte r + lngLenEntity - 1
End If
End If
Next i
varInput = strOutput
HTMLEntityRepla ce = strOutput
exitRoutine:
Exit Function
End Function