469,268 Members | 942 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,268 developers. It's quick & easy.

memo field in report truncating at 255 characters

4
I have a memo field in a report that is truncating at 255 characters. I've tried the following w/no success: 1. Grow/Shrink option is yes. 2. "Group By" changed to "first". 3. "Group by" changed to "last" 4. took out memo field, saved query, and reinserted memo field. nothing seems to be fixing this bug. Please help! any suggestions are greatly appreciated.
Jan 27 '07 #1
21 24878
I have a memo field in a report that is truncating at 255 characters. I've tried the following w/no success: 1. Grow/Shrink option is yes. 2. "Group By" changed to "first". 3. "Group by" changed to "last" 4. took out memo field, saved query, and reinserted memo field. nothing seems to be fixing this bug. Please help! any suggestions are greatly appreciated.
Does the memo field display correcty in your table? If so, it may be the query itself that is the cause of the problem. Would you mind posting the query?
Jan 27 '07 #2
NeoPa
32,171 Expert Mod 16PB
Good for you JLC (I really can't write jenkinsloveschicken every time can I :)).
It's always good to see Junior Members helping with answers. In this case I suspect the OP is having the problem as they are trying to display the item in a report. Reports don't have Memo controls. IE. They must be trying to display a Memo field using a TextBox - for which the maximum length is 255 characters.
You may also be on the right lines with the query though - I'm not sure that would support anything greater than 255 characters either.
Jan 27 '07 #3
Jenn
4
I think I finally figured it out. I did have a memo field (not text field). The memo field seems to display all characters on current information, but was not working for older data that was imported into access from excel. As long as my current info is displaying, it's not a big problem anymore. thanks so much for the advice & tips.
-Jenn


Good for you JLC (I really can't write jenkinsloveschicken every time can I :)).
It's always good to see Junior Members helping with answers. In this case I suspect the OP is having the problem as they are trying to display the item in a report. Reports don't have Memo controls. IE. They must be trying to display a Memo field using a TextBox - for which the maximum length is 255 characters.
You may also be on the right lines with the query though - I'm not sure that would support anything greater than 255 characters either.
Jan 28 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
I think I finally figured it out. I did have a memo field (not text field). The memo field seems to display all characters on current information, but was not working for older data that was imported into access from excel. As long as my current info is displaying, it's not a big problem anymore. thanks so much for the advice & tips.
-Jenn
At a guess I would say that the older data that was imported from excel has some illegal carriage return characters that access can't interpret. If you view the data in the table can you see some rectangle characters mixed in with the text?
Jan 28 '07 #5
missinglinq
3,532 Expert 2GB
I use a fair number of memo fields because of working in the healthcare field; we're big on Progress Notes and such. Because of this I'm always looking out for anything on the topic. The following is a distillation of things I've picked up over the past few years. To give you an idea, I keep it in a file titled MemoFieldAngst!

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.

The bottom line is, if you use memo fields keep it simple! No fancy formatting and no fancy data manipulation!
Jan 28 '07 #6
NeoPa
32,171 Expert Mod 16PB
I think I finally figured it out. I did have a memo field (not text field). The memo field seems to display all characters on current information, but was not working for older data that was imported into access from excel. As long as my current info is displaying, it's not a big problem anymore. thanks so much for the advice & tips.
-Jenn
I was aware that you were using a Memo Field in your table. However, unless you know something I don't, there is no Memo Control in either a Report or a Form. IE. Assuming your first post was accurate and you were having this problem in a Report, you were trying to display a Memo Field via a TextBox Control, which has a limit of 255 characters.
I hope that clears up any confusion :)
Jan 28 '07 #7
NeoPa
32,171 Expert Mod 16PB
We're going to have to set up a The Wisdom of MissingLinq thread obviously. I went to stick the new post and add it to the Tutorials section, only to find it done already (Memo Fields in Access). I thought Memo Field Angst a better title, but hey...
Jan 28 '07 #8
MMcCarthy
14,534 Expert Mod 8TB
We're going to have to set up a The Wisdom of MissingLinq thread obviously. I went to stick the new post and add it to the Tutorials section, only to find it done already (Memo Fields in Access). I thought Memo Field Angst a better title, but hey...
Talk to me and I'll explain how I did it.
Jan 28 '07 #9
NeoPa
32,171 Expert Mod 16PB
I will, but you're not online atm - even in TSDN so not just the program StartUp missing ;)
Jan 29 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
I will, but you're not online atm - even in TSDN so not just the program StartUp missing ;)
My niece's 18th birthday. I took a break.
Jan 29 '07 #11
NeoPa
32,171 Expert Mod 16PB
I have a niece having an 18th next month. I'm a bouncer - lol.
Of course you're allowed a break. You can look forward to another one next year too :D
Jan 29 '07 #12
MMcCarthy
14,534 Expert Mod 8TB
I have a niece having an 18th next month. I'm a bouncer - lol.
Of course you're allowed a break. You can look forward to another one next year too :D
One a year, you're too generous and that's enough hijacking of this thread.
Jan 29 '07 #13
Thanks for this thread!

Although it didn't have a way around the problem if indeed you do need to use a complicated query, it gave me enough info to find a way, so I thought I'd post.

Just create a simple query for that notes field and a report for just that field and use that report as a subreport in your report that must have a complex query to run it.

For instance, I am creating Order reports, but there are notes on each order that were being trunacated, so I created a simple query with just OrderID and NotesText, then created a report for just the NotesText field (and allowed it to grow or shrink) and then back to my complicated (not really, just grouping) query/report and created a subreport with the OrderID linked to the subreport (also allowed to grow or shrink).

Works sweetly!

Hope that helps someone else googling for answers!

c.
Nov 7 '07 #14
NeoPa
32,171 Expert Mod 16PB
Thanks Cindy.
We always appreciate more answers in threads. Different approaches nearly always prove valuable, even when they're not right for the current problem. Most of our traffic is actually from people who find threads by searching generally and most answers prove helpful for someone :)
Nov 8 '07 #15
Ericks
74
I have a memo field in a report that is truncating at 255 characters. I've tried the following w/no success: 1. Grow/Shrink option is yes. 2. "Group By" changed to "first". 3. "Group by" changed to "last" 4. took out memo field, saved query, and reinserted memo field. nothing seems to be fixing this bug. Please help! any suggestions are greatly appreciated.
Choosing the option "First" instead of "Group by" in the Totals field is what I would do. The name of the field will now start with FirstOf when in datasheet view. Make sure you change this in the report.
Nov 8 '07 #16
I had a very similar but more confusing issue and since this was the closest match on a web search I thought I would post here to save others the pain and immense confusion.

Using Access 2003 which does allow more than 255 characters in text areas on both forms and reports I had a report which was not showing the FIRST 255 characters of a text column. The data was from a view based on a VARCHAR(2000) column in SQL server using linked tables. A few other tables and views were inner joined but none reference this column.

I copied the query from the record source to a new basic query just to eliminate that and still had the issue. Showing the contents of the linked view or a simple SELECT * showed everything.

So, Starting from scratch I slowly built up the query, testing at each new addition to see where the problem occurred.

It turns out it was the last thing for me to test. I had DISTINCT instead of DISTINCTROW and that was the source of everything.
May 20 '10 #17
OldBirdman
675 512MB
You all need more breaks :)
A textbox control will display more than 255 characters. This is true for both bound and unbound textboxes. If it could not, there would be no way to either enter or view a memo field from a form.
Of course, if a textbox is bound, but not to a memo field, the size of the table field will limit the size allowed in the control.
May 20 '10 #18
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.

Thanks! My problem disappear after editing the query: without "distinct" it works good.
Apr 6 '13 #19
GregHB
2
One of my team members just had this issue yesterday (6-9-14). Thanks, everyone, for this thread. After reviewing your comments, I was able to fix the issue with her query.

As it turns out, she had "Totals" enabled and that was causing her Notes (Memo) field to be truncated.

I could see the data in the tables. Calculated fields showed the data in plain text: "Left([Notes],64000)" Type = Memo / plain text (Type = Text resulted in truncated fields in the table). But, each time the query would run, the fields would be cut off at 255 characters; sometimes in mid-sentence, other times in mid-word.

Sorry if I've revived a long-dormant thread, but this was extremely helpful to me and my team. We were able to publish a complete report to our federal client in a timely manner thanks to this thread.
Jun 10 '14 #20
NeoPa
32,171 Expert Mod 16PB
Posting to a dormant thread is only a problem when either :
  1. The subject matter is no longer appropriate. EG. Maybe how Access works has changed since then.
  2. An attempt is made to ask for more information - or even a separate question.
Thank you notes are never inappropriate and I suspect any members still around after this time will still appreciate the comment.
Jun 22 '14 #21
Thank you very very very much, I have solved it, by removing the Group by.
May 21 '16 #22

Post your reply

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

Similar topics

2 posts views Thread by Chris | last post: by
3 posts views Thread by John | last post: by
7 posts views Thread by midlothian | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
2 posts views Thread by Teresa L | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.