Converting Non-English Keyboard Characters

by Patrick L. Steiner

Not all applications are created equal—and neither are keyboards! We sometimes take for granted that everyone uses U.S./English computer standards. If non-English users work with your Access application, chances are that their keyboards and data-entry values will be different for many characters.

In this article, we'll show you how to write a function that will convert various non-English keyboard characters into English equivalents. You can use our routine to "clean" data in tables or filter input data on forms. Let's begin by briefly reviewing the ASCII codes that correspond to special characters.

An ASCII review

The standard ASCII character set comprises 256 codes or characters. The lower 128 characters (0 through 127) are codes generated by any U.S.-style keyboard. For instance, if you press lowercase n, your keyboard generates ASCII code 110.

The high-order characters of the ASCII set (128 through 255) consist of special symbols and foreign characters. For example, the Spanish ñ corresponds to ASCII code 241, while the French é is ASCII code 233.

Fortunately for users, international keyboards support these necessary foreign characters without requiring special keystrokes. However, these special characters can present data-entry and retrieval issues that developers and programmers must deal with. For instance, should you allow users to store high-order characters in your database? And, how will queries retrieve the data?

Our technique answers these questions for you. First, you'll build a table that stores all the ASCII codes. Next you'll write a function that enters the codes and corresponding characters; you'll manually add to each record a replacement character for use when you don't want to allow that particular foreign character. A second function will translate non-English characters for you by replacing them with the appropriate translation values from your table. Let's get started.

Creating a character-translation table

Begin by building a table to store the 256 ASCII codes. Each code record will include a replacement value for use if necessary. Table A shows the structure of the tblAsciiCharacters table—think of it as a character-translation table.

Table A: tblAsciiCharacters table structure

FieldData type

Next, you'll write a function to fill in the values of the first two fields in each tblAsciiCharacters record. In the third field, you'll enter the English equivalent of any characters you want to replace. Using this approach, you can allow certain high-order characters and disallow others—for instance, you might want to let users enter the character ½ (code 189).

Rather than typing all 256 ASCII codes, you can write a VBA function to enter them for you. Listing A contains the PrintAsciiCharacterValues() function. Basically, it uses the RunSQL command to delete any existing records in tblAsciiCharacters. Then, the function opens the table and adds 256 records via the For…Next loop. For each record, the code writes the ASCII character (text) value using the Chr$() function. Then, it assigns the character's ASCII via the Asc() function.

Listing A: Function to enter values into the ASCII translation table

Function PrintAsciiCharacterValues() As Integer

'----------------------------------------------
' Purpose:  Create an ASCII table to allow 
' character replacements for high-order 
' characters in our application.
'----------------------------------------------
' By:       Pat Steiner
' Date:     3/22/98
' Version:  1.0
'----------------------------------------------

Dim DB As Database
Dim RS As Recordset

Dim sMessage As String
Dim sFunction As String
Dim sMsgTitle As String
Dim sSQL As String

Dim i As Long

'----------------------------------------------
' Initialize local variables
'----------------------------------------------
sFunction = "PrintAsciiCharacterValues"
sMsgTitle = "FN: " & sFunction & " ( )"
PrintAsciiCharacterValues = 0   'Initial value

'----------------------------------------------
' Define error trap label
'----------------------------------------------
On Error GoTo Err_PrintAsciiCharacterValues

'----------------------------------------------
' Clear any existing entries from ASCII table
'----------------------------------------------
sSQL = "Delete * From tblAsciiCharacters;"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings False

'----------------------------------------------
' Add new entries to ASCII table
'----------------------------------------------
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("tblAsciiCharacters")

For i = 0 To 255
  RS.AddNew
  RS![AsciiCharacter] = Chr$(i)
  RS![AsciiValue] = Asc(Chr$(i))
  RS.Update
Next i

RS.Close
PrintAsciiCharacterValues = -1  'Success value
Exit Function

'=============================
Err_PrintAsciiCharacterValues:
'=============================
Select Case Err

  Case Else:
    Close
    Beep
    sMessage = "Error #" & Err & ": " & _
               Error(Err)
    MsgBox sMessage, vbCritical, sMsgTitle
    Exit Function
    
End Select
End Function

Now, run the PrintAsciiCharacterValues() function via the debug window to generate entries for tblAsciiCharacters. Then, open the table and make entries in the ReplacementChar field for any values you wish to translate. Note that if you leave the ReplacementChar field blank, our technique won't translate that record's non-English character—it will use the original character.

Figure A shows our tblAsciiCharacters table with sample data. As you can see, we've entered replacement characters only for selected entries; our program will allow the other non-English characters, so the ReplacementChar field is blank in those records.

Removing selected characters

Next you'll write the TranslateCharacters() function, shown in Listing B, which removes (translates) characters. This function scans an input string (sText) character by character, replacing each character with the value found in tblAsciiCharacters. The function itself is declared as a string, so its value is set to the "translated" character string upon successful completion.

Listing B: Character-translation function and its global variable declarations

Option Compare Database
Option Explicit

Global gArrayLoaded As Integer
Global gAsciiValue(255) As Integer
Global gReplacementString(255) As String

Function TranslateCharacters(sText As String) _
  As _String

'----------------------------------------------
' Purpose:  To remove any high-order characters
' from the input string [sText].
'----------------------------------------------
' By:       Pat Steiner
' Date:     3/22/98
' Version:  1.0
'----------------------------------------------

'----------------------------------------------
' Declare function variables
'----------------------------------------------
Dim DB As Database
Dim RS As Recordset

Dim sMessage As String
Dim sFunction As String
Dim sMsgTitle As String
Dim sSQL As String
Dim sTemp As String * 1
Dim sReplace As String

Dim iLen As Integer
Dim i As Integer

Dim lValue As Long

'----------------------------------------------
' Initialize local variables
'----------------------------------------------
sFunction = "TranslateCharacters"
sMsgTitle = "FN: " & sFunction & " ( )"
TranslateCharacters = ""

'----------------------------------------------
' Define error trap label
'----------------------------------------------
On Error GoTo Err_TranslateCharacters

'----------------------------------------------
' Check if global array is loaded into memory
'----------------------------------------------
If IsNull(gArrayLoaded) Or gArrayLoaded <> _
  -1 Then
  
  Set DB = CurrentDb()

  sSQL = "SELECT * FROM tblAsciiCharacters  _
    ORDER BY AsciiValue;"
  
  Set RS = DB.OpenRecordset(sSQL, _
    dbOpenDynaset)
  
If Not RS.EOF Then
    RS.MoveFirst
    Do Until RS.EOF
      gAsciiValue(RS![AsciiValue]) = _
        RS![AsciiValue]
      If Not IsNull(RS![ReplacementChar]) Then
        gReplacementString(RS![AsciiValue]) = _
          RS![ReplacementChar]

      Else
        gReplacementString(RS![AsciiValue]) = _
          RS![AsciiCharacter]

      End If
      RS.MoveNext
    Loop
  End If
  RS.Close
  gArrayLoaded = -1
End If

'----------------------------------------------
' Check input string for each character
'----------------------------------------------
If Not IsNull(sText) Then
  iLen = Len(sText)
  For i = 1 To iLen
    sTemp = Mid(sText, i, 1)
    lValue = Asc(sTemp)
    
    sReplace = gReplacementString(lValue)
    
    TranslateCharacters = TranslateCharacters _
      & sReplace

  Next i
End If
Exit Function

'=============================
Err_TranslateCharacters:
'=============================
Select Case Err

  Case Else:
    Close
    Beep
    sMessage = "Error #" & Err & ": " & _
      Error(Err)
    MsgBox sMessage, vbCritical, sMsgTitle
    Exit Function
    
End Select
End Function

To make this function more efficient, we've used some global variables. These variables stay in memory during the entire Access session and are available to any subroutine or function within the application. The global variable gArrayLoaded indicates whether tblAsciiCharacters has been loaded into the global arrays gAsciiValue and gReplacementString. These global arrays are dimensioned to 255—they're zero-based and will hold 256 entries. They hold the information from tblAsciiCharacters that we need for translating the sText input string.

Our TranslateCharacters() function first checks the value of gArrayLoaded to see whether the global arrays have been loaded—that is, whether gArrayLoaded has a value of -1. If not, the code reads tblAsciiCharacters and loads the global arrays with the values in the table, then sets gArrayLoaded equal to -1. If gArrayLoaded has a value of -1, the code doesn't need to read tblAsciiCharacters. Using this technique, you need to read tblAsciiCharacters only once, no matter how many times you need to translate character strings.

Next, TranslateCharacters() checks the sText input string to see whether it's NULL. If it is, you exit the function and do nothing. Otherwise, the code scans each character in the string and sets it equal to the character equivalent as stored in your global arrays. The function value TranslateCharacters is built by concatenating the translated characters one by one.

Testing the TranslateCharacters() function

To test our function, you'll create a tblData table with the structure shown in Table B. Populate tblData with the names, addresses, and phone numbers shown in Figure B.

Table B: tblData table structure

Next, create a form called frmSearchData and make tblData its recordsource table. The form's detail section displays data in a continuous fashion, as shown in Figure C. To achieve this display, set the form's Default View property to Continuous Forms.

The form's header contains four unbound text boxes: txtFirstName, txtLastName, txtAddress, and txtCity. Enter the code from Listing C in the appropriate text box's After Update event property. The user will enter search criteria into these text boxes; then, the TranslateCharacters() function will translate the data and remove any special characters.

Listing C: Code behind the four search criteria text boxes

Private Sub txtAddress_AfterUpdate()

If Not IsNull(Me![txtAddress]) Then
  Me![txtAddress] = _
    TranslateCharacters(Me![txtAddress])
End If

End Sub

Private Sub txtCity_AfterUpdate()

If Not IsNull(Me![txtCity]) Then
  Me![txtCity] = _
    TranslateCharacters(Me![txtCity])
End If

End Sub


Private Sub txtFirstName_AfterUpdate()

If Not IsNull(Me![txtFirstName]) Then
  Me![txtFirstName] = _
    TranslateCharacters(Me![txtFirstName])
End If

End Sub


Private Sub txtLastName_AfterUpdate()

If Not IsNull(Me![txtLastName]) Then
  Me![txtLastName] = _
    TranslateCharacters(Me![txtLastName])
End If

End Sub

The form's header also contains a command button named btnSearch. Enter the code shown in Listing D into the button's Click event. This code calls function BuildSQLWhere(), which reads the form's search criteria text boxes and builds a SQL WHERE statement to find the data. The code for BuildSQLWhere() appears in Listing E.

Listing D: Code behind the Search button

Private Sub btnSearch_Click()

Dim sWhere As String
Dim sSQL As String

sWhere = BuildSQLWhere()
If sWhere = "" Then
  sSQL = "Select * From tblData"
Else
  sSQL = "Select * From tblData Where " & _
    sWhere
End If

sSQL = sSQL & " Order By LastName, FirstName;"

Me.RecordSource = sSQL
Beep

End Sub

Listing E: Function BuildSQLWhere

Function BuildSQLWhere() As String

'----------------------------------------------
' Purpose:  To read the following search
' variables from <frmSearchData> and build a 
' SQL "where" clause to query <tblData>:
'
'                 [txtFirstName]
'                 [txtLastName]
'                 [txtAddress]
'                 [txtCity]
'----------------------------------------------
' By:       Pat Steiner
' Date:     3/22/98
' Version:  1.0
'----------------------------------------------

'----------------------------------------------
' Declare function variables
'----------------------------------------------
Dim F As Form

Dim sMessage As String
Dim sFunction As String
Dim sMsgTitle As String
Dim sWhere As String
Dim sData As String

'----------------------------------------------
' Initialize local variables
'----------------------------------------------
sFunction = "BuildSQLWhere"
sMsgTitle = "FN: " & sFunction & " ( )"
sWhere = "("
BuildSQLWhere = ""

'----------------------------------------------
' Define error trap label
'----------------------------------------------
On Error GoTo Err_BuildSQLWhere

'----------------------------------------------
' Begin function logic
'----------------------------------------------
Set F = Forms![frmSearchData]

If Not IsNull(F![txtFirstName]) Then
  If sWhere <> "(" Then
    sWhere = sWhere & " And "
  End If
  sWhere = sWhere & "(FirstName Like " & _
    Chr$(34) & "*" & F![txtFirstName] & "*" & _
    Chr$(34) & ")"
End If

If Not IsNull(F![txtLastName]) Then
  If sWhere <> "(" Then
    sWhere = sWhere & " And "
  End If
  sWhere = sWhere & "(LastName Like " & _
    Chr$(34) & "*" & F![txtLastName] & "*" & _
    Chr$(34) & ")"
End If

If Not IsNull(F![txtAddress]) Then
  If sWhere <> "(" Then
    sWhere = sWhere & " And "
  End If
  sWhere = sWhere & "(Address Like " & _
     Chr$(34) & "*" & F![txtAddress] & "*" & _
     Chr$(34) & ")"
End If

If Not IsNull(F![txtCity]) Then
  If sWhere <> "(" Then
    sWhere = sWhere & " And "
  End If
  sWhere = sWhere & "(City Like " & _
    Chr$(34) & "*" & F![txtCity] & "*" & _
    Chr$(34) & ")"
End If

If sWhere <> "(" Then
  sWhere = sWhere & ")"
Else
  sWhere = ""
End If

BuildSQLWhere = sWhere

Exit Function

'=================
Err_BuildSQLWhere:
'=================
Select Case Err

  Case Else:
    Close
    Beep
    sMessage = "Error #" & Err & ": " & _
               Error(Err)
    MsgBox sMessage, vbCritical, sMsgTitle
    Exit Function
    
End Select

End Function

After you create the test form and function BuildSQLWhere, open the form in form view mode. To test it, enter ón into the LastName field, as shown in Figure D. (On a U.S. keyboard, enter the ó character by holding down the [Alt] key and entering 162 on the numeric keypad.)

Now, click the Search button and watch what happens. Because we call our translation routine on the After Update event property (for the search criteria text boxes), the text boxes are immediately parsed and translated. As shown in Figure E, the form now displays four (matching) records instead of the original six. Also, notice that the value in the LastName field is now on, without the accent mark.

Notes

Using this technique, you can also prevent users from entering certain low-order characters into your application. In particular, the control characters (codes 0 through 31) can cause problems when they're stored as data in tables—this occasionally occurs when data is imported from another application or source.

To clean existing data in tables, you can write an update query or VBA routine that replaces the original text field value with the translated value. For example:

     [Text] = TranslateCharacters([Text])

Finally, remember that you need to make entries in the ReplacementChar field of tblAsciiCharacters only for characters you want to translate. Leave this field blank for all other characters.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.