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:
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