Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Report field sizing

    I have a basic Access 2010 report, with a simple query that just lists a handful of fields from a single table, and a report that just lists the query.

    The list includes four memo fields that contain variable amounts of data; some are empty, some contain hundreds of characters. However, some of the memo fields appear truncated on the report, although they are not truncated in the query output.

    The memo fields all have "Can grow = Y" in the report properties, and the truncation is not consistent; e.g., one record has a 411-character memo field truncated at 248 characters but a 449-character one not truncated at all.

    What could be causing this?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The most common cause of truncation of memo fields is some sort of sort or other function being applied to the data as a part of the process of building the report. It can be an explicit sort in the underlying query, or it can come from implicit sorting that occurs in GroupBy or Union queries. The other possibility is that the "can grow" property doesn't get it quite right - you could try making the text box much larger than it needs to be to see if that is the case.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. The query uses just a single table (so no union) with no totalling (so no grouping), and the report has no grouping or sorting. The query does have a sort on one field (not one of the memo fields), and I tried it without the sorting, but it made no difference.

    I also tried increasing the height of the memo fields in the report, and switching off the "Can grow" option, but it made no difference; the same fields are always truncated in the same places (not in the query but in the report).
    Last edited by Murgatroyd; 2016-06-04 at 20:56.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Have you tried making the table the data source for your report (with no sorting)? It certainly sounds like the query is what is truncating the fields.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    In addition to what Wendell mentioned, make sure you haven't tried to format the field. Also, make sure the detail section "Can Grow" property is True.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies, which led me to an answer to the original question but raised a further one.

    1. Original issue: When I compared the output of (a) the query, (b) the report with the query as the source, and (c) the report with the table as the source, the issue was the same: the memo fields were not truncated in (a) but were truncated in (b) *and* (c). This seemed to confirm that the problem was in the report rather than the query.

    I confirmed that the "Can grow" option was True on all the memo fields, so that did not seem the be the problem.

    However, on checking more closely, I noticed that the truncation was only occurring on one field, and on checking the properties, I found that this one was inadvertently formatted as a date (oops!), and clearing that solved the problem. Now, the memo fields are not truncated when viewing the report in datasheet view or when exported to a PDF; however, the truncation still occurs when the report is exported to an Excel file, which appears to be a different issue, as follows.

    2. Further issue: when exporting the report, I can select either "Excel 5.0/95" or "Excel 97/2003" format (both .xls files), but the truncation occurs with both. When exporting the query, there are the same two options, and the truncation again occurs with both, but I can also select "Excel Workbook" format (.xlsx file), and the truncation does not occur with this.

    The Excel export function also has an "Export data with formatting and layout" option, but with reports it is greyed out (why?), and with queries the truncation occurs in .xls files but does not occur in .xlsx files whether this option is ticked or not.

    Does this indicate a bug with the Excel .xls export options, and why is the Excel .xlsx export option (which works) available for queries but not for reports?
    Last edited by Murgatroyd; 2016-06-05 at 20:35.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    In Excel 2003:
    If a cell contains more than 1,024 characters, or if it contains a formula that returns a result of more than 1,024 characters, only approximately the first 1,024 characters are displayed in the cell.

    I've not been able to find whether or not this also applies to newer versions.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. All of the memo fields contain well under 1,024 characters (the longest is 692 characters); however, on further investigation, I found that these fields are all being truncated at 255 characters when exported to an .xls file, and this appears to be a technical limitation somewhere.
    https://support.microsoft.com/EN-US/kb/294286

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •