Report field sizing

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote