ACC2000: Sample Function to Replace or to Remove Special Characters in an MDB (210433)

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 to replace the converted tabs and other special characters, or to remove these special characters from the field.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

MORE INFORMATION

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

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.

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

Create a query based on the table to which the text file was imported.
Add the field containing the special characters to the Query Design grid.
Convert the query to an update query by clicking Update Query on the Query menu.
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.
Run the query.

To remove converted special characters, follow these steps:

Follow all steps that replace special characters previously, except replace the procedure in step 2 earlier with the following:

'============================================================
' The following function will:
'   - Find the tabs in a Text or Memo field.
'   - Call another function to remove 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 = RemoveTabs(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 removes tabs
' from the field. It returns the updated text.
'==================================================================   
Function RemoveTabs(intstart As Integer, strText As String) As String
   Dim front As String, rear As String
   front = Left(strText, intstart - 1)
   rear = Mid(strText, intstart + 1)
   RemoveTabs = front & rear
End Function