Memo field gets truncated in query results.

[AccessBeetle]

Hello,
I am using Access 2007. I have created a query which is the recordsource of one of the reports. This query is just a simple query without any conditions on any fields, it does have multiple tables. When I run the query the memo field gets truncated and that is why in the report it does not show full amount of text. How to fix this problem?

Thanks for any help!!

[TheSmileyCoder]

Do you have joins on any memofields? That can cause this error (atleast in 2003).

And your sure the truncation is in the query, and not just that the reports control has CanGrow=False? :)

[NeoPa]

I would check carefully where this is happening. First run the query standalone to see if it is actually truncated there. It may well be that the TextBox control on the report is actually the limiting factor here.

[missinglinq]

Crosstab Queries, Summary Queries, Union Queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates.

Also, if you have specified a format in the field's Format property, this will often truncate the data as well. If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated.

Allen Browne has a page with a more extensive explanation and workarounds

http://allenbrowne.com/ser-63.html

Linq ;0)>

[AccessBeetle]

Everyone,

I have run the query and reports seperately. The query does not show the memo filed fully and that is why it is not showing in the report also,

Also, the memo fields are kind of huge text, so they are not the joining fields or anything like that.

On the report, textbox's Can grow and Can Shrink properties are set to yes.

Missinglinq
by

If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated.

Where should I check for this?

Also, the format property on report and table it self are set to nothing. They are empty
Here is my query.

SELECT lkptbl_ProjectID.ProjectID, lkptbl_ResourceCategory.ResourceCategory, lkptbl_ResourceCategory.ResourceCategoryID, [B]tblPerformanceReporting.ProgressAccomplishments, tblPerformanceReporting.DifficultiesDelays, tblPerformanceReporting.Deliverables[/B], [Grant Information].[project long title]
FROM lkptbl_ResourceCategory INNER JOIN (lkptbl_ProjectID INNER JOIN ([Grant Information] LEFT JOIN tblPerformanceReporting ON [Grant Information].[Grant Index] = tblPerformanceReporting.[Grant/Performance Index]) ON lkptbl_ProjectID.ProjectID_ID = [Grant Information].ProjectID_ID) ON lkptbl_ResourceCategory.ResourceCategoryID = [Grant Information].ResourceCategoryID;

The fields in bold are the memo fields.
Let me know what I am still doing wrong because it is not showing the full text yet.

[NeoPa]

Have you checked out Linq's link yet? I suggest this should be your next step.

BTW Well done for responding to all the posts. It's much easier to work with people who do that :)

[AccessBeetle]

Yes NeoPa, I checked the link of allenbrowne. Although, none of the situations were matching. I rewrite the same query again and deleted the previous one and now my query shows the whole text but not the report.
any inputs on that??

[AccessBeetle]

OK I got it fixed. I changed the design of the report. Instead of putting labels in the header and text in the details, I tried to put both in the detail section and elongate the textbox until the full width. That did some trick and I can see all the text now. Except for one problem, I see only 1/4 of the page is filled and rest of it is empty. This is because of some of the property set on report which enforces it not to break any data and that is why the whole chunk of data is forwarded to the another page leaving the previous page half or more empty.
Thanks guys as always.

[NeoPa]

You're welcome :) Well done for finding what worked.