Memo field truncated in report if report contains sorting and grouping

[Kurt]

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!

 

[Allen Browne]

Hi Kurt.

You seem to have a good handle on what's going on here. The
Sorting'n'Grouping probably is causing grouping on the memo field behind the
scenes, which will truncate it at 255 characters.

If you do not need grouping, you may be able to get around this by just
sorting on the field, or using the report's OrderBy.

If you do need grouping, a subreport based on a query without a GROUP BY
clause should work, or you may be able to use a subquery to get the memo
field after the grouping is completed (though Access tends to complain about
multi-level grouping when you do this for reports.)

 

[Kurt]

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.