Report field is truncated?

[Daniel]

Good morning,

In a report I have a field, which is populated by a query, which get it's
content truncated at 255 chrs.

I read in other post to change the data type to memo, this is the case in
the source table, but it does not appear as an option in the query nor in the
report field properties.

What do I ned to change to manage to display the full content of the source
in the report field?

Thank you for your help!

Daniel

 

[Allen Browne]

If this field is already a Memo in your table, make sure there is nothing in
the Format property of the field in the table, the Format property of the
field in the query, or the Format propery of the text box on the report. The
formatting characters will truncate it.

If that is not the issue, then your query is probably aggregating on the
memo field. For example, you might have a GROUP BY clause in your query
(Total query), or a DISTINCT predicate in the query statement.

If it is a Totals query, try choosing First instead of Group By under the
memo field. This allows Access to return the whole thing instead of having
to group the query results by the memo field.

 

[Daniel]

Allen,

by changing the group by statement to First like you suggested the
information is properly being gathered by my query. However it still is
being truncated in the report?

The fields is a complexe series of iif() statement to test for nulls/empty
entries and adjust the content displayed. Would this have an impact? Are
there work arounds?

If the IIF() and Is Null() functions aren't the cause of my problem, any
ideas what I may have missed?

Daniel

 

[Allen Browne]

I take it that the query is now showing the full memo, but the report is
not.

Presumably this is more than just setting the CanGrow property of the text
box on the report to give it enough space?

Perhaps the report is doing something that requires it to aggregate on this
field. For example, do you have the memo field in the Sorting And Grouping
dialog?

 

[Daniel]

Allen,

Yes, the report has both sorting and grouping. Why is this a problem? I
don't quite grasp why this has any impact on populating a field?

More importantly, is there a work around because I absolutely require the
sorting and grouping and of course the full content of the memo field?

Thank you for your time,

Daniel

 

[Rick Brandt]

Allen,

Yes, the report has both sorting and grouping. Why is this a
problem? I don't quite grasp why this has any impact on populating a
field?

More importantly, is there a work around because I absolutely require
the sorting and grouping and of course the full content of the memo
field?

Thank you for your time,

You can have sorting and grouping AND the full contents of a Memo field.
You just can't sort or group ON the Memo field itself.

A memo field is a special DataType where the actual data is not stored in
the table, but only a pointer to it is. This is what allows (virtually)
unlimited characters to be entered into them, but this difference also means
that some of the things you can do with other DataTypes are not allowed
against Memo fields.

In Access 97 and older versions you would simply get an error when even
attempting these dis-allowed operations. In Access 2000 and newer they
allow them by truncating the Memo field and performing the operation only on
the first 255 characters.

 

[Daniel]

Rick,

I am not actually grouping or sorting on the memo field itself. However, it
is inside another groups Header. Should this work none the less?

Since this seems to create a problem, is there another approach to allowing
user to enter large quantities of textual information that is easier to work
with?

Thank you,

Daniel

 

[Duane Hookom]

You can try the "First" trick again by setting the control source to:
=First([YourFieldName])
or use a subreport to display the memo field.

 

[Rick Brandt]

Rick,

I am not actually grouping or sorting on the memo field itself.
However, it is inside another groups Header. Should this work none
the less?

I don't recall off-hand, but I suspect that should be okay. Tempoarily move it
to the detail section to see if the truncation goes away. If not, then
something else is doing it.

Since this seems to create a problem, is there another approach to
allowing user to enter large quantities of textual information that
is easier to work with?

Not really no. In these situations you need to divide and conquer. You've
already confirmed that the data in the query is not truncated right? If it is
then there is no point in messing with the report. If it does not truncate in
the query then create a brand new plain vanilla report based on the same query
with nothing in it but a single TextBox containing the memo field. Does that
truncate? If not then it is definitely something about your report design that
is doing it. Make a copy of it and start de-constructing it (deleting objects,
sections, groupings, etc.), until you see the truncation eliminated.