473,692 Members | 2,216 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Truncated MEMO fields in Access 2007 !!!

First of all - all answers that I could find via Google do not give me any explicit info as to how to handle this problem. So please - do not paste the following link for the hundreth time:


... instead I would really, really appreciate if someone could ultimately state whether:

1) ...there is a way of importing Excel file to Access without truncating text fields containing above 255 characters? Assumption - all pre-eliminary preparations like properly chosen data types (MEMO) in Access are already in place!

2) ... queries in Access cannot transfer more than 255 characters from MEMO fields?

3) ... forms and reports cannot cope with the same problem?

In conclusion this is really ridiculous that in XXI century the biggest technology corporation on the planet cannot create soft that would be allow users to perform simple operations on fields containing more than 255 characters !!!
Oct 7 '10 #1
8 7748
3,080 Recognized Expert Specialist
Hi Jimmy,

To answer your questions:
1) Yes, a straight forward import will create a new table with a Memo field for that, but just make sure the >255 cell is in the first 8 rows or use an existing table with a memo field.
2) They can, as long as you don't manipulate them (don't use string functions or GroupBy)
3) No problem, as long as you place the Memo field from the field list. The unbound text box will be 255 max!

Oct 7 '10 #2
32,569 Recognized Expert Moderator MVP
  1. There is. I tried it and it worked first time without issue (Access 2003).
  2. This is not true. There are circumstances where it will fail, but this is not a fundamental truth.
  3. Forms and reports can cope with >255 characters perfectly well.

Jimmy Jones:
In conclusion this is really ridiculous that in XXI century the biggest technology corporation on the planet cannot create soft that would be allow users to perform simple operations on fields containing more than 255 characters !!!
You link to a very good article and complain it doesn't answer your questions, yet I found that it gives a fair idea and a few quick tests were all that was required to confirm. From this quoted text I can only assume you didn't bother to read it, as it explains clearly why your rant makes no sense, and why such a limit, in the circumstances where it is applied, is not only sensible, but practically unavoidable for a desktop database engine like Jet.
Oct 7 '10 #3
Jimmy Jones
10 New Member
Thanks for quick replies!

With regard to:
"This is not true. There are circumstances where it will fail, but this is not a fundamental truth."

... could you outline these circumstances ? Maybe I'm doing something wrong but each time I run a query to combine (meaning to put side by side) MEMO fields from different tables I get the same results - only first 255 characters appear...

Oct 7 '10 #4
3,080 Recognized Expert Specialist
Check out the UNION solution from the link I'm not allowed to post... :-)

Oct 7 '10 #5
32,569 Recognized Expert Moderator MVP
As Nico says, this point is covered in exhaustive detail in the article by Allen Browne that you posted a link to. It even explains why it is that the various scenarios listed each suffer from this truncation. I consider myself pretty good with words generally, but I can't see how I could explain it any better than it appears there.
Oct 7 '10 #6
Jimmy Jones
10 New Member
Thanks man :)

I knew it would come to this :) However, when I click the UNION button the whole SQL code disappears...
Why it happens?

By the way I'm not an IT guy... so that is why I am a little bit frustrated by this. I'm learning Access from scratch and I'm under pressure so I apologize for sarcastic remarks...
Oct 7 '10 #7
32,569 Recognized Expert Moderator MVP
I'm not sure I follow you Jimmy. This might make sense if your SQL is the data you're working with. I don't know. I've never noticed a UNION button in Access. Are you working on your SQL directly? I assume you appreciate from the linked article, that any data returned from a UNION query will be truncated anyway yes? The work-around for this (and in many cases the more appropriate version anyway) is to use UNION ALL instead.

I hope this helps but I'm really not sure where you're at ATM.
Oct 7 '10 #8
3,080 Recognized Expert Specialist
The UNION button isn't known by me, but a NION can only be intered in SQL (text) mode as the graphical query editor can't show two (or more) select statements.

Sometimes I create first the "basic" queries and use a UNION like:
Expand|Select|Wrap|Line Numbers
  1. select * from qry1
  2. UNION 
  3. select * from qry2

Oct 7 '10 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Hugh Welford | last post by:
Hi ... using WIN XP/ FP 2000/ IIS. I need to be able to retieve variable length (>255 characters) messages which have been saved in a memo field in an access data base on the server. I am using the following code to test this : - <% set objconn = server.createobject ("ADODB.connection") set objrec = server.createobject ("ADODB.recordset") objconn.open "DSN=daters"
by: skinnybloke | last post by:
Hi - I have a problem with a memo field being truncated to about 255 characters when running a Access 2002 query. This only seems to happen if I use SELECT DISTINCT. It works ok using SELECT by itself. does anyone know why this happens and how to stop it?
by: jacoballen | last post by:
I have a query that combines the results of three related tables. The memo fields are truncated to 255 characters, but I need all the information in them. I'm aware that removing code such as DISTINCT and GROUPBY will fix this problem (as discussed in other threads in this group), but I need to limit the query results to unique values, which is what DISTINCT does for me. Any suggestions? Thanks, Jacob
by: Allen Browne | last post by:
If you are looking for opinon on what's useful in Access 2007, there's a new article at: http://allenbrowne.com/Access2007.html Covers what's good (useful features), what's mixed (good and bad), what's gone (features removed), what's fixed (old issues solved), what's broken (new bugs), configuration, compatibility, should you buy, and links. It is opinion, so you may disagree, but hopefully it's an informative summary.
by: Neil | last post by:
I posted a few days ago that it seems to me that the Access 2007 rich text feature does not support: a) full text justification; b) programmatic manipulation. I was hoping that someone might know one way or the other whether that was true or not, or could point me to an article or help text that would. What I have seen so far online and in Access 2007 help seems to confirm the above. But that (or at least (b)) seems incredible that it...
by: ARC | last post by:
This is taken from the "What's New in Access 2007" page. However, I've looked through all the properties of a text field memo box, and cannot find the append only option. Does anyone know how to use this feature? ------------------ "Memo fields are useful for storing large amounts of information. With Office Access 2007, you can set the Append Only property to retain a history of all changes to a Memo field. You can then view a history...
by: ARC | last post by:
Just curious if anyone is having issues with Acc 2007 once the number of objects and complexity increases? I have a fairly large app, with many linked tables, 100's of forms, queries, reports, and lots of vba code. I'm nearly finished with re-doing my app in access 2007, and just imported an add-in program, which has added even more forms, queries and linked tables. Every so often now, after opening many different screens, I'll...
by: data monkey | last post by:
My company's web application uses mySQL databases. I have linked these tables (Read only) into Access 2007 so I can create queries and reports. Now I need to extract some of the content from these linked tables so that I may do a mail merge into Word. Unfortunately, I find that many of the fields are being truncated. I tried importing the tables and still find the fields are truncated. So neither importing or linking is bringing in the...
by: anthony | last post by:
Is there any way that individual words or phrases can be formatted within a memo field? Obviously, the whole field can have formatting applied but I'm interested in being able to format just part of the field. This will be in a report Many thanks
by: Max | last post by:
Hi I am trying to export an Access table to a fixed-width text file. The table contains 2 memo fields of approx. 1000 characters each. I am using the 'Export text wizard' to export. In this wizard I change the default width (512) of the memo fields to the required larger size but it still only exports as 512. This has always worked fine for me in Access 2003 but it is truncating
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.