Memo field truncating in report

[Laurie]

I have a problem with a memo field truncating in a report.

The field originates from a query designed to return the "last comment".
The base table contains multiple entries for each entity, with date/time
fields for each entry. The query returns the max date and comment. This
query is then nested into another to return the max time (for that date).

The SQL for the report links this query to the entity table to obtain entity
detail accompanied by the most recent comment.

I've verified none of the underlying queries are grouping by the memo field
and properties of the field and detail section are all set to "can grow".
When I display the results of from the SQL, the memo field is NOT truncated -
however - it IS on the report.

Help!

 

[Laurie]

I should have noted that the Entity table is linked to this query one to many
(there could be entities without comments....). Not sure if this is causing
the problem?

 

[Marshall Barton]

I have a problem with a memo field truncating in a report.

The field originates from a query designed to return the "last comment".
The base table contains multiple entries for each entity, with date/time
fields for each entry. The query returns the max date and comment. This
query is then nested into another to return the max time (for that date).

The SQL for the report links this query to the entity table to obtain entity
detail accompanied by the most recent comment.

I've verified none of the underlying queries are grouping by the memo field
and properties of the field and detail section are all set to "can grow".
When I display the results of from the SQL, the memo field is NOT truncated -
however - it IS on the report.

If the report's record source query returns the untruncated
memo field, then the only other reason I am aware of is the
memo's text box has something in its Format property.

It shouldn't be possible, but also check that the memo field
is not used in Sorting and Grouping.

 

[Dale Fye]

I've verified none of the underlying queries are grouping by the memo field
If the memo field is included in any of the aggregate queries, it will get
truncated.

Generally, I'll create a query that identifies the last comment or something
like this:

SELECT T2.*
FROM yourTable as T2
INNER JOIN (SELECT ID, Max(DateField) as LatestComment
FROM yourTable
GROUP BY ID) as T
ON T.ID = T2.ID
AND T.LastestComment = T2.DateField

Can you post the SQL of the report and any of the queries it uses.

Another way to approach this might be to use a DLOOKUP statement in the
ControlSource of the memo textbox (on the report), rather than binding that
textbox to a field in the query. Something like:

ControlSource: = DLOOKUP("Comment", "yourTable", _
"ID = " & me.txt_ID & " AND " _
"[DateField] = #" & me.txt_Date &
"#")

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.