Convert Unicode characters into HTML Ascii with function in a Query (1 Viewer)

world33

Registered User.
Local time
Tomorrow, 01:01
Joined
Oct 24, 2006
Messages
21
Hello,

I would like to convert some UNICODE foreign languages special characters into HTML ASCII codes by using a function in an Access query.
For example the following field text string

Code:
Escuela Administración

needs to be converted into

Code:
Escuela Administraci & # 2 4 3 ; n
(pls disregard the spaces within the ascii code; I could not find a way to show the code without being automatically converted into the ó character)

and so on for many other Unicode characters in different languages.

I managed to find this function by Andrew MacKillop in the commnet section of the following webpage https://blog.brettski.com/2009/12/04/vba-convert-unicode-to-ascii/

Code:
Public Function UnicodeToAscii(sText As String) As String
 Dim x As Long, sAscii As String, ascval As Long

If Len(sText) = 0 Then
 Exit Function
 End If

sAscii = ""
For x = 1 To Len(sText)
 ascval = AscW(Mid(sText, x, 1))
 If (ascval < 0) Then
 ascval = 65536 + ascval ' http://support.microsoft.com/kb/272138
 ElseIf (ascval < 32) Then ' non print characters
 sAscii = sAscii & "&#" & ascval & ";"
 ElseIf (acval = 34 Or acval = 39 Or acval = 38 Or acval = 60 Or acval = 62) Then 'reserved characters " ' &
 sAscii = sAscii & "&#" & ascval & ";"
ElseIf (ascval > 127) Then ' unicode
 sAscii = sAscii & "&#" & ascval & ";"
Else ' acceptable text characters
 sAscii = sAscii + Mid(sText, x, 1)
 End If
 Next
 UnicodeToAscii = sAscii
 End Function

It seems to manage to do the job however for blank fields it returns "#Error" messages instead of blank fields.
Moreover when I try to filter any of those generated fields I get "Data type mismatch in criteria expression".

My question are:
1) Is the above function the most comprehensive function to convert Unicode to Html Ascii available?
2) How can I fix the "#Error" message populated in the blank fields?
3) How can I fix the "Data type mismatch in criteria expression" error when filtering?

Thanks a lot for any help
 
Last edited:

JHB

Have been here a while
Local time
Today, 15:01
Joined
Jun 17, 2012
Messages
7,732
..
My question are:
1) Is the above function the most comprehensive function to convert Unicode to Html Ascii available?
I don't know, but it seems to work okay, I see you've more code lines as in the original code.
..
2) How can I fix the "#Error" message populated in the blank fields?
You can use IsNull, (to check if the field is empty), in combination with IIF.
Code:
IIf(Not IsNull([YourFieldWithUnicodetext]);UnicodeToAscii([YourFieldWithUnicodetext]))
.. 3) How can I fix the "Data type mismatch in criteria expression" error when filtering?
How and where do you filter it, (I can't reproduce the error)?
 

Users who are viewing this thread

Top Bottom