Memo field truncated in report when using sorting and grouping

[datamaestro]

I have a memo field "description" in a table. I bring this field into a query using First to make sure that it does not truncate (it does if using Group By). The query result is not truncated, as expected. So far, so good.

If I build a report off of this query and just use straight columns with no sorting or grouping, the whole field displays correctly. I have Can Grow = Yes, so it grows as much as necessary to display the whole memo field value. So far, so good.

As soon as I add a sorting and grouping level, such as "status," the "description" field gets truncated at 255 characters (or so, I haven't counted them). As soon as I remove the sorting and grouping levels, the "description" field displays correctly again. I am NOT adding the memo field to a header or trying to sort on it, but having any sorting and grouping in the report seems to cause this behavior.

I have read a lot about truncation issues when exporting memo fields to Excel or when using Group By on a memo field in a query, but those do not apply here. Has anyone else come across this issue? Any ideas for how to get around it? Any advice would be greatly appreciated. Thanks!

 

[THWatson]

datamaestro
There seem to be issues with memo fields in reports. One of the issues is that if there is any formatting applied to a memo field it truncates at 255 characters, because formatting treats a memo field as a text field which has a limit of 255. Article 259893 in the Microsoft Knowledge Base talks about this.

Also, I notice a couple of other threads on this forum. They are 703-449022 and 703-755469. Maybe there is something in the those that will point you in the direction of how to fix the problem.

Tom

 

[datamaestro]

I fixed it. The original query included calculations as well as memo fields. I separated them out into separate queries, one for all text and memo fields, one for all numeric and calculated fields, and then joined them back together in final query to be used in report. The final query does no calculation or totalling of any kind. This worked.