ACC97: Memo Field Truncated When Report Is Output to MS Excel 97

• Microsoft Access 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you output a report to Microsoft Excel 97, any Memo field is truncated to 255 characters. You may also see this behavior when you click the Analyze It With MS Excel command while previewing a report.

CAUSE

In Microsoft Excel 97, the maximum length of text-cell contents is 32,000 characters. However, Microsoft Access 97 outputs a report to Microsoft Excel 5.0/95 format, in which the maximum length of text-cell contents is 255.

RESOLUTION

Method 1

Follow these steps to export the underlying table or query to Microsoft Excel 97 file format.

1. In the Database window, select the table or query on which the report is based.

2. On the File menu, click Save As/Export.

3. In the Save As dialog box, click To An External File Or Database, and then click OK.

4. In the Save Table dialog box, select Microsoft Excel 97 (.xls) in the Save As Type box, and then click Export.

Method 2

Create text boxes on the report to split the Memo field into 250-character strings. After you have output the report, reassemble those strings.

For example, assume you have a Memo field named Notes, and in some records the contents of that field is 700 characters long. You can create three text boxes in the report with each of the following three expressions as the control source for one of the text boxes:

   =Mid([Notes],1,250)

   =Mid([Notes],251,250)

   =Mid([Notes],501,250)

   =CONCATENATE(B2,C2,D2)

Notice that the CONCATENATE function returns the error #VALUE when you try to use the formula

   =CONCATENATE(B2:D2)

If the length of the data in the Memo field makes it necessary for you to create more than a few text boxes, you can use a Visual Basic for Applications procedure to automate the creation of those text boxes. The following example demonstrates how to use a procedure to create the text boxes, and then output the report and reassemble the contents of the text box.

  1. Follow steps 1 - 7 in the "Steps to Reproduce Behavior" section later
     in this article.

  2. Create a new report in Design view based on the tblMemoOutput table.

  3. Drag the ID field to the Detail section of the report.

  4. Save the report as rptMemoOutput.

  5. Open a new module and type the following procedure:

       Function MemoSplitter(strReportName As String, _
          strFieldName As String, lngMemoLength As Long)

          Dim NewControl As Control
          Dim intLoopCount As Integer
          For intLoopCount = 0 To lngMemoLength / 250

             Set NewControl = CreateReportControl(strReportName, _
                acTextBox, acDetail)
             NewControl.Name = intLoopCount & "MemoText"
             NewControl.ControlSource = "=Mid([" & _
                strFieldName & "]," & 250 * intLoopCount + 1 _
                & ",250)"
          Next intLoopCount

       End Function

  6. Compile and save the module as mdlSplitFunction.

  7. Press CTRL+G, type the following in the Debug window, and then press
     ENTER:

        ?MemoSplitter("rptMemoOutput","Notes",5200)

  8. View the report in Print Preview. On the Tools menu, point to Office
     Links and click Analyze It With MS Excel.

  9. When the data appears in a spreadsheet file in Microsoft Excel, click
     cell A1; press CTRL+SHIFT+END to select all of the data.

 10. On the Format menu, point to Row and click Autofit.

 11. Select cell A2. On the Insert menu, click Rows.

 12. Type the following formula in cell A2:

        =VALUE(LEFT(A1,FIND("M",A1,1)-1))

 13. Copy the formula to cells B2 - U2.

 14. Select cells A2 - U2.

 15. On the Edit menu, click Copy.

 16. On the Edit menu, click Paste Special.

 17. Under Paste in the Paste Special dialog box, click Values, and then
     click OK. This converts the formulas in the second row to values which
     you can use to sort the segments of the Notes field.

 18. Click cell A2 and press CTRL+SHIFT+END to select all but the first row
     of data.

 19. On the Data menu, click Sort.

 20. In the Sort dialog box, click Options.

 21. Under Orientation in the Sort Options dialog box, click Sort Left To
     Right. Then click OK.

 22. In the Sort dialog box, click Row2 in the Sort By list, and then click
     OK to sort the segments of the Notes field.

 23. In cell W3, type the following formula. Remove the underscore (_) so
     that the formula appears without spaces on one line.

        =CONCATENATE(A3,B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,_
           L3,M3,N3,O3,P3,Q3,R3,S3,T3,U3)

 24. Copy the formula to cells W3 and W4 to reassemble the Notes field for
     each record.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access and open any database.

  2. Create the following table named tblMemoOutput:

       Table: tblMemoOutput
       --------------------
       Field Name: ID
          Data Type: Text
       Field Name: Notes
          Data Type: Memo

  3. Save the table as tblMemoOutput. When prompted to create a primary
     key, click No.

  4. Open the table in Datasheet view and enter the following records:

       ID   Notes
       --   -----

       a    a
       b    b
       c    c

  5. Open a new module and type the following procedure:

       Function FillMemo(strTableName As String, _
          strFieldName As String)

          Dim db As Database
          Dim rs As Recordset
          Dim intLoopCount As Integer
          Set db = CurrentDb
          Set rs = db.OpenRecordset(strTableName)
          Do Until rs.EOF
             rs.Edit
             For intLoopCount = 1 To 26
                rs(strFieldName) = rs(strFieldName) _
                   & String(200, Chr(intLoopCount + 64))
             Next intLoopCount
             rs.Update
             rs.MoveNext
          Loop
          db.Close

       End Function

  6. Compile and save the module as mdlDataFunction.

  7. Enter the following in the module's Debug window, and then press
     ENTER:

       ?FillMemo("tblMemoOutput","Notes")

  8. Use the AutoReport: Columnar Wizard to create a report based on
     tblMemoOutput. View the report in Print Preview.

  9. On the Tools menu, point to Office Links and click Analyze It With MS
     Excel.

 10. When the data appears in a spreadsheet file in Microsoft Excel, type
     the following formula into cell C2:

        =LEN(B2)

     Note that the data in the Notes field has been truncated to 255
     characters.

REFERENCES

For more information about loading the output of a report into Microsoft Excel, search the Help Index for "outputting data" and display the topic "Load the output of a datasheet, form or report into Microsoft Excel."