Query changing Data Type from long text to short text

[Galette]

I am using Link Table between Ms Access and Outlook
I wanted to create a query keeping only 3 fields (Subject, Contents and Received)
This query is creating a new Table called New _Table

SELECT my_table.Subject, LEFT(my_table.Contents, 1000), my_table.Received INTO New_Table
FROM my_table;

Contents field has a data type set to Long Text but when running my query is changing to short text and it is keeping only 255 char. I wanted to be able to have 1000 char.

What I am missing ?

 

[sneuberg]

I tried this type of query and found the LEFT function causes that. I suggest just working around this by taking the LEFT out. If you want to trim down the Contents field to 1000 characters you can do this with an UPDATE query.

I'm curious. Why do you want to do this? 1 TB Drives only cost about $55.

 

[Galette]

The content field has more than 15000 characters and It is making my other queries running 10 times faster if I only keep 1500 or 1000.

How you will do it with Update Statement ?

 

[arnelgp]

put the memo field first in your query:

SELECT LEFT(my_table.Contents, 1000), my_table.Subject, my_table.Received INTO New_Table
FROM my_table;

 

[sneuberg]

I tried putting the memo field first it my test case and I still ended up with short text.

You could try:

SELECT my_table.Subject, my_table.Contents, my_table.Received INTO New_Table
FROM my_table;

Followed by

UPDATE my_table SET my_table.Contents = LEFT(my_table.Contents, 1000);

I don't know about the query speeds, but to recoup any space you will need to do a compact and repairs.

But a better way would be to create New Table with the fields you want then instead of a make table query like you have. Then delete all of the records in New Table each time and fill it with an append query. After you have the New_Table you would run

DELETE * FROM New_Table;

Followed by

INSERT INTO New_Table (Subject,Contents,Received )
SELECT my_table.Subject, LEFT(my_table.Contents, 1000), my_table.Received
FROM my_table;