Given by John W. Vinson in a previous post

Just be sure that you avoid the things that truncate memo fields:

- Sorting on the memo field
- Grouping By the memo field in a Totals query
- Using a DISTINCT or UNIQUE VALUES property in the query
- setting a Format (even something as simple as >) on the memo field

       

Can you build the table first?  Then you can define the field type etc.

Using a MakeTable query, I'm not sure you can force the data type to a
memo field.

THe Mid function is causing the truncation in this make table query.
Solution one.
Just import the field as is and then use an update query to trim off the
first 10 characters.

SELECT (Select Max(ID) from 01_LCControl where ID<tbl.ID) AS ID
  , [Field1] AS Contents
INTO 15_Contents
FROM [Mod Data] AS Tbl
WHERE (((Tbl.Field1) Like "Contents:*"));

UPDATE 15_Contents
SET Contents = Trim(Mid([Contents],10))

Solution two
Design the table first and specify the field type as memo
Then you can use an append query to add data and if you need the table
cleared, use a delete query to clear out all the records.

I know of no way to force the memo field type in the destination table
if you are going to manipulate the memo field in the Select statement.

'====================================================
 John Spencer
 Access MVP 2002-2005, 2007
 Center for Health Program Development and Management
 University of Maryland Baltimore County
'====================================================