Microsoft KB Archive/109825

Knowledge Base

ACC: Sample Function to Replace Special Characters

Article ID: 109825

Article Last Modified on 1/18/2007

---

APPLIES TO

• Microsoft Access 1.0 Standard Edition
• Microsoft Access 1.1 Standard Edition
• Microsoft Access 2.0 Standard Edition
• Microsoft Access 95 Standard Edition
• Microsoft Access 97 Standard Edition

---

This article was previously published under Q109825

Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

When you import a text file that contains tabs or other special characters into Microsoft Access, the special characters are converted and displayed as boxes. You cannot use the Find and Replace commands to find these converted characters. This article describes a sample Visual Basic for Applications procedure that you can use to find and replace the converted tabs and other special characters.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

MORE INFORMATION

To find and replace converted special characters, follow these steps:

1. Create a module and type the following lines in the Declarations section:

         Option Compare Binary
            ' Otherwise, the function will replace spaces with percent signs.
         Option Explicit
                       

2. Type the following procedures:

         '============================================================
         'The following function will:
         ' - Find the tabs in a Text or Memo field.
         ' - Call another function to replace the tabs.
         '============================================================
   
         Function FindTabs (WhichField As String) as String
            Dim x As Integer, strText As String
            Dim start As Integer
               start = 1
               x = 1
               strText = WhichField
   
               Do Until x = 0
                  ' Chr(9) is the Tab character.
                  ' Replace Chr(9) with the ANSI code for the character
                  ' you are searching for.
                  x = InStr(start, strText, Chr(9))
                  start = x + 1
                  If x > 0 And Not IsNull(x) Then
                     strText = ReplaceTabs(x, strText)
                  End If
               Loop
   
               FindTabs = strText
         End Function
   
         '==================================================================
         ' The following function is called from the FindTabs() function. It
         ' accepts two arguments, strText and start. The function replaces
         ' tabs with %. It returns the updated text.
         '==================================================================
   
         Function ReplaceTabs(start As Integer, strText As String) As String
            ' Replace % with the character you want to substitute.
            Mid(strText, start, 1) = "%"
            ReplaceTabs = strText
         End Function
                       

3. Create a query based on the table to which the text file was imported.
4. Add the field containing the special characters to the Query Design grid.
5. Convert the query to an update query by clicking Update Query (or Update in version 1.x, 2.0, or 7.0) on the Query menu.
6. Add the following to the Update To row for the field(s) containing the special characters

   FindTabs(<[fieldname]>)

   where <[fieldname]> is the name of the field containing the special characters.
7. Run the query.

REFERENCES

For more information about ANSI characters, search for "ANSI" using the Microsoft Access 97 Help Index.

Additional query words: find/replace recognize

Keywords: kbhowto kbprogramming kbusage KB109825

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.