ACC: Sample Function to Replace Special Characters

MORE INFORMATION

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.