Microsoft KB Archive/210433

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

Article ID: 210433

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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. </li> Run the query.</li></ol>

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

Additional query words: find/replace recognize

Keywords: kbinfo kbprogramming kbusage KB210433

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.