Memo field is truncated when you output a report to excel in Access 2002

Back to the top

Method 1

1. In the Database window, select the table or query on which the report is based.
2. On the File menu, click Export.
3. In the Export Table '<Table Name>' dialog box, select Microsoft Excel 97-2002 (*.xls) from the Save as Type box.
4. Click Export All to export the table.

Back to the top

Method 2

1. Follow steps 1 through 7 in the "Steps to reproduce behavior" section later in this article.
2. Create a new report in Design view that is based on the tblMemoOutput table.
3. Drag the ID field to the detail section of the report.
4. Save the report as rptMemoOutput. Keep the report open in Design view.
5. Open a new module, and then 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 then save the module as mdlSplitFunction.
7. Type the following line in the Immediate window, and then press ENTER:

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

8. View the report in print preview, and note the three IDs: a, b, and c. On the Tools menu, point to Office Links, and then click Analyze It with Microsoft Excel.
9. When the data appears in a spreadsheet file in Microsoft Excel, click cell A1, and then press CTRL+SHIFT+END to select all of the data.
10. On the Format menu, point to Row, and then 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 through U2.
14. Select cells A2 through 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 then 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, and 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:
    =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 W4 and W5 to reassemble the Notes field for each record.

Back to the top

Steps to reproduce the behavior

1. Start Microsoft Access, and then open any database.
2. Create the following table:

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

3. Save the table as tblMemoOutput. When you are prompted to create a primary key, click No.
4. Open the table in Datasheet view, and then enter the following records:

      ID   Notes
      --   -----
      a    a
      b    b
      c    c
   					

5. Open a new module, and then 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 then save the module as mdlDataFunction.
7. Enter the following line in the Immediate window, and then press ENTER:

   ?FillMemo("tblMemoOutput","Notes")
   					

8. Use the AutoReport: Columnar Wizard to create a report that is based on tblMemoOutput. View the report in Print Preview.
9. On the Tools menu, point to Office Links, and then click Analyze It With Microsoft 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.

Back to the top