ACC2000: Imported Excel Carriage Returns Become Square Boxes (210372)

RESOLUTION

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. To resolve this behavior, you can convert the carriage return characters used in Excel to those used in Access. To create a user-defined function to do that, follow these steps:

1. Start Microsoft Access and open any database.
2. In the Database window, click Module, click New, and then type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type the following procedure:

   Function ChangeStr (strOriginal As Variant, strOldChar As String, strNewChar As String, intMatchCase As Integer) As Variant
      ' This function changes all substrings strOldChar in string strOriginal
      ' to strNewChar.
      ' The parameter intMatchCase has the same purpose as in the
      ' InStr() function, i.e. 1 makes the function case-sensitive, 0 does not
      Dim temp As String, pos As Integer
      temp = ""
      If IsNull(strOriginal) Then
         ChangeStr = Null
         Exit Function
      End If
      If strOldChar = "" Or strOriginal = ""  Then
         ChangeStr = strOriginal
         Exit Function
      End If
   
      pos = InStr(1, strOriginal, strOldChar, intMatchCase)
      While pos > 0
         temp = temp & Mid$(strOriginal, 1, pos - 1) & strNewChar
         strOriginal = Right$(strOriginal, Len(strOriginal) - pos - Len(strOldChar) + 1)
         pos = InStr(1, strOriginal, strOldChar, intMatchCase)
      Wend
      ChangeStr = temp & strOriginal
   End Function
   					

4. Create a new query in Design view, and add the table containing the inappropriate text data.
5. On the Query menu, click Update Query.
6. Drag the field that you want converted to the query design grid.
7. In the Update To row of the query design grid, type

   ChangeStr([fieldname],Chr$(10),Chr$(13)& Chr$(10),0)

   where fieldname is the name of the field that you want to convert.
8. Run the query.

Notice that text is now presented as separate lines with carriage returns.

NOTE: You may need to increase the row height in Datasheet view to observe the multiple lines.