Re: Change SQL to allow Memo format rather than only 255 character tex
- From: John Spencer <spencer@xxxxxxxxxx>
- Date: Sun, 01 Jul 2007 11:40:53 -0400
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
'====================================================
Pete wrote:
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:*"));
- Prev by Date: Re: Union Queries and Foreign Keys
- Next by Date: A way to find "no match" when two columns needed for linking?
- Previous by thread: Re: query from two different tables with no common themes
- Next by thread: A way to find "no match" when two columns needed for linking?
- Index(es):
Relevant Pages
|
