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

Article translations Article translations
Article ID: 210433 - View products that this article applies to.
This article was previously published under Q210433
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

Expand all | Collapse all

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:

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

Properties

Article ID: 210433 - Last Review: October 11, 2006 - Revision: 3.2
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbinfo kbprogramming kbusage KB210433

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com