Truncated MEMO fields in Access 2007 !!!

[Jimmy Jones]

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:

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

... 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 !!!

[nico5038]

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!

Nic;o)

[NeoPa]

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.

[Jimmy Jones]

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...

Thanks!

[nico5038]

Check out the UNION solution from the link I'm not allowed to post... :-)

Nic;o)

[NeoPa]

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.

[Jimmy Jones]

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...

[NeoPa]

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.

[nico5038]

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:

select * from qry1
UNION 
select * from qry2
 

Success

Nic;o)