473,320 Members | 2,080 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Memo field gets truncated in query results.

111 100+
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)>

8 18679
TheSmileyCoder
2,322 Expert Mod 2GB
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
32,556 Expert Mod 16PB
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
3,532 Expert 2GB
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
AccessBeetle
111 100+
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
32,556 Expert Mod 16PB
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
AccessBeetle
111 100+
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
AccessBeetle
111 100+
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
32,556 Expert Mod 16PB
You're welcome :) Well done for finding what worked.
May 10 '10 #9

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

Similar topics

1
by: Rich | last post by:
Ok I am trying to read a memo field from an Access DB that has more than 255 char. I am only getting 255 chars back from the reader. Why is this and how do i get the whole memo field. Here is a...
3
by: GorDon | last post by:
Hi, I have a report based on a query. The query grabs a memo field from my main table, yet when I display the memo field in the report it truncates the memo field (the field needs to hold more...
1
by: Rose | last post by:
I have an access 2000 database. In it I have a table OPEN Items which has a field Issue that is setup as a MEMO field. I am trying to append information from another table with the same setup,...
4
by: Jack | last post by:
THE FOLLOWING IS A PART OF CODE FROM A ASP PAGE <% sql01 = "SELECT COUNT(*) AS reccount FROM Equipmenttbl " sql01 = sql01 & "WHERE Equipmenttbl.GrantID = " & GrantID 'Response.Write sql01 &...
12
by: Jack | last post by:
Since, I have not got some desired advise, I am reposting this for some asnwer/valuable suggestion. Thanks. THE FOLLOWING IS A PART OF CODE FROM A ASP PAGE <% sql01 = "SELECT COUNT(*) AS...
2
by: Teresa L | last post by:
I am using Access 2003. I have a memo field that I have put into a report. My problem is that the report will only show a specific amount of characters in the report. It does not display the...
2
Ericks
by: Ericks | last post by:
Why is it that a table's Memo field in a Query behaves like a Text field, showing only the first 255 characters when using the Totals option. When turning off the Totals options all text is shown. I...
0
by: johnvonc | last post by:
I am using Access 2003, and trying to Export query results in code to a csv file. I am using DoCmd.TransferText. When I run the query, there are two memo fields: Description and...
3
by: Pravs | last post by:
I have a database wherein a lot of fields are updated by my team on a daily basis. When they are working on a lot of rows of same patient and certain fields have to be filled with same data, to save...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.