Change SQL to allow Memo format rather than only 255 character tex

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following SQL seems to cut my Memo text to fewer characters. My knowledge
is too thin to recognise which part is doing it. Help would be much
appreciated. The ID part is Ok, the problem lies in Field1. Thanks

SELECT (Select Max(ID) from 01_LCControl where ID<tbl.ID) AS ID,
Trim(Mid([Field1],10,Len([field1]))) AS Contents INTO 15_Contents
FROM [Mod Data] AS Tbl
WHERE (((Tbl.Field1) Like "Contents:*"));
 
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
'====================================================
 
Delighted. Solution 1 works a treat. Thank you.

John Spencer said:
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
'====================================================

The following SQL seems to cut my Memo text to fewer characters. My knowledge
is too thin to recognise which part is doing it. Help would be much
appreciated. The ID part is Ok, the problem lies in Field1. Thanks

SELECT (Select Max(ID) from 01_LCControl where ID<tbl.ID) AS ID,
Trim(Mid([Field1],10,Len([field1]))) AS Contents INTO 15_Contents
FROM [Mod Data] AS Tbl
WHERE (((Tbl.Field1) Like "Contents:*"));
Click to expand...
Click to expand...
 
Back
Top