Memo Field truncated in report 2

[TapeMan]

I am running a report which takes data from a two tables, the main table has a memo filed that reports fine, the sub table (One main to many sub) has a memo field that randonmly truncates the memo field. The truncation is not at any particular length, if I add some text (just garbage) to an affected field, it then prints the whole of the original text, but leaves out the additions!

This is driving me mad - any help is appreciated! I've tried increasing the size of the box in the report and this is partially succesful but results in an ugly report with lots of space.

Access 2002 BTW, no SP.

 

[Tom81]

You might try setting the "Can Grow" format property of the field to Yes. That will cause the field to auto expand when there is more text in the field than you have given room for.

 

[TapeMan]

Thanks Tom, but I already had that set. Without set to NO, I just get a single line for each entry (as I would expect).

 

[dhookom]

Please provide the SQL of the report's record source. Queries with the phrases "DISTINCT" or "DISTINCTROW" or "GROUP BY" will clip memo fields to 255 characters. A format property value in the text box will also do this.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TapeMan]

SQL for the report query is:-

SELECT Issues.ID, Issues.Country, Issues.Product, Issues.Problem, Issues.Status, Issues.Customer, Updates.Date, Updates.Update, Updates.Action, Updates.[Update ID], Issues.Case, DateDiff("d",Issues!OpenDate,Date$()) AS Expr1, Issues.OpenDate
FROM Issues INNER JOIN Updates ON Issues.ID = Updates.ID
WHERE (((Issues.Status)="Open&quot)
ORDER BY Updates.Date;

The data looks OK in the results of the query, just appears wrong in the report.

 

[dhookom]

THe SQL shouldn't be a problem. Do you see the full data when you open the datasheet view of the report's record source(s)? Do you have any format property set on the text boxes? Is your report section set to Can Grow?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sxschech]

I'm also having a trunction report problem. When I use a Select query, it runs fine, when I use a Union query, it truncates. The reason I started using a Union query is because I needed to display the full name (AssignedMember) along with the memo field data. The names are stored in a different table, one for active members and one for former members. Would a Dlookup perhaps solve this problem? If so, what would the syntax be. Responses.Series is the Memo field. Here is the SQL statement as it stands now...

SELECT Responses.E_id, [member_lastname] & ", " & [member_firstname] AS AssignedMember, Responses.response, Responses.series
FROM Responses INNER JOIN Member_List ON Responses.member_id = Member_List.member_id
WHERE (((Responses.e_id)=[Forms]![frmParticipants].[cboE]))
UNION SELECT Responses.e_id, [member_lastname] & ", " & [member_firstname] AS AssignedMember, Responses.response, Responses.series
FROM Responses INNER JOIN Member_List_Former ON Responses.member_id = Member_List_Former.member_id
WHERE (((Responses.e_id)=[Forms]![frmParticipants].[cboE]));

Thank You.

 

[dhookom]

I would create a union query of the Member_List and Member_List_Former. Add this union query to a query with Responses.

Actually I wouldn't keep two separate tables but I doubt that is an option for you.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[sxschech]

Thanks Duane, your answer worked great. It seems so obvious and something I should have thought of sooner. Still seems strange that the union query won't allow memo to display fully without using an additional query.

 

[dhookom]

It kinda makes sense. A standard union query will remove duplicate values. This suggests your result is a "group by". Try to group by a memo field.

You might have gotten around the issue by using "UNION ALL" but I am not sure. A "UNION ALL" would not attempt to remove duplicates.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[TapeMan]

I 'fixed' my problem by recreating the report. The design is exactly as before, but the new one does not truncate the memo fields! Same font, same font size etc. etc. Same query for the source data.
I have also copied and edited the report (with a slightly different query) to produce a report on another category and that also works fine!

 

[yooshik]

Duane,

UNION ALL does allow more than 256 characters from the query to be returned. You are the man!!!

Tim

 

[qwertygiacomo]

The original article on Microsoft.com can be found here.

Microsoft Legal Links Terms of use Security & Privacy Accessibility

ACC2000: Error Using OLE or Memo Field in Union Query
View products that this article applies to.
This article was previously published under Q208926
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).


SYMPTOMS
When you run a SQL-specific union query that contains an OLE Object field, you may receive the following error message:


Cannot use Memo, OLE, or Hyperlink Object field '<fieldname>' in the SELECT clause of a union query.


You observe this behavior when the Access database uses Microsoft Jet 4.0 Service Pack 5 or earlier.
CAUSE
By default, union queries implicitly sort the data and delete duplicate records. Because Memo, OLE, and Hyperlink Object fields cannot be sorted, the error occurs.
RESOLUTION
To avoid this error, add the ALL predicate to eliminate the sorting of the field data. For example, add the ALL predicate to the following SQL statement SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees
UNION
SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees;

to produce the statement: SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees;
UNION ALL
SELECT DISTINCTROW Employees.[LastName], Employees.[Photo]
FROM Employees;

Note that the SQL statement with the ALL predicate does not remove duplicate records.
REFERENCES
For more information about union queries, click Microsoft Access Help on the Help menu, type What is an SQL query and when would you use one in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

The information in this article applies to:
Microsoft Access 2000
Last Reviewed: 10/21/2003 (2.0)
Keywords: kberrmsg kbprb KB208926 kbAudDeveloper


Microsoft Legal Links Terms of use Security & Privacy Accessibility