By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

Memo field gets truncated in query results.

100+
P: 111
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!!
May 10 '10 #1

✓ answered by 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)>

Share this Question
Share on Google+
8 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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? :)
May 10 '10 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 10 '10 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
May 10 '10 #4

100+
P: 111
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
Expand|Select|Wrap|Line Numbers
  1. If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated.
  2.  
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT lkptbl_ProjectID.ProjectID, lkptbl_ResourceCategory.ResourceCategory, lkptbl_ResourceCategory.ResourceCategoryID, tblPerformanceReporting.ProgressAccomplishments, tblPerformanceReporting.DifficultiesDelays, tblPerformanceReporting.Deliverables, [Grant Information].[project long title]
  2. 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.
May 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
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 :)
May 10 '10 #6

100+
P: 111
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??
May 10 '10 #7

100+
P: 111
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.
May 10 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome :) Well done for finding what worked.
May 10 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.