ACC2000: Sample Function to Replace Special Characters in a MDB

• Microsoft Access 2000

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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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 if they are not already there:




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

2. Type the following procedure:




'============================================================
' 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 intCounter As Integer
   Dim strText As String
   Dim intStart As Integer
   
   intStart = 1
   intCounter = 1
   strText = WhichField
   
   Do Until intCounter = 0
      ' Chr(9) is the Tab character.
      ' Replace Chr(9) with the ANSI code for the character
      ' you are searching for.
      intCounter = InStr(intStart, strText, Chr(9))
      intStart = intCounter + 1
      If intCounter > 0 And Not IsNull(intCounter) Then
         strText = ReplaceTabs(intCounter, strText)
      End If
   Loop
   
   FindTabs = strText
End Function

'==================================================================
' The following function is called from the FindTabs() function. It
' accepts two arguments, intStart and strText. The function replaces tabs
' with %. It returns the updated text.
'==================================================================

Function ReplaceTabs(intStart As Integer, strText As String) As String
   ' Replace % with the character you want to substitute.
   Mid(strText, intStart, 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 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.

Additional query words: find/replace recognize

Keywords : kbprg kbusage kbdta AccCon KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbinfo