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

This article was previously published under Q210433
This article has been archived. It is offered "as is" and will no longer be updated.
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 charactersinto Microsoft Access, the special characters are converted and displayedas boxes. You cannot use the Find and Replace commands to find theseconverted characters. This article describes a sample Visual Basic forApplications 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 Declarationssection if they are not already there:
    Option ExplicitOption 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 = strTextEnd 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 = strTextEnd 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 = strTextEnd 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 & rearEnd Function				
find/replace recognize
Properties

Article ID: 210433 - Last Review: 12/05/2015 11:31:29 - Revision: 3.2

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbinfo kbprogramming kbusage KB210433
Feedback