Having problems creating a new table

[platypus71]

I inherited an Access DB with tons and tons of stuff in it already, so I am still figuring out what everything is and does.

Here is one of the current problems I am looking in to:
I have a query that brings up all of the information I want written into a new table. One of the fields is a MEMO field and can get pretty lengthy.

When my second query runs, which creates a table with the results from the first query, it truncates the MEMO field from above to around 255 characters.

Is there a way to get it to stop truncating? Or is this a built-in (hidden) limit to Access?

Thanks.

 

[PHV]

A memo field is truncated when you use either (at least) DISTINCT, UNION (without ALL) or any function with the memo field as argument.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[platypus71]

The table is generated with
INSERT INTO TableName ( Field1, Field2, etc, FieldBAD)
SELECT QueryThatWorks.Field1, QueryThatWorks.Field2, QueryThatWorks.etc, QueryThatWorks.FieldBAD
FROM QueryThatWorks
GROUP BY QueryThatWorks.Field1, QueryThatWorks.Field3, QueryThatWorks.FieldBAD
HAVING (((QueryThatWorks.Field4)<900000));

etc represents 12 more fields. FieldBAD is the one that is getting truncated.

Keep in mind, QueryThatWorks outputs to the screen just fine. It is the query that puts it into this table that truncates it.

 

[PHV]

The culprit is here:
GROUP BY ..., QueryThatWorks.FieldBAD

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[platypus71]

Thanks. Next question is:
What do I need to do to get the query to work? (or is it not possible?)

If I remove it from the list, I get an error that says it "does not include the specified expression as part of an aggregate function."

 

[PHV]

Why not using this ?
INSERT INTO TableName ( Field1, Field2, etc, FieldBAD)
SELECT QueryThatWorks.Field1, QueryThatWorks.Field2, QueryThatWorks.etc, QueryThatWorks.FieldBAD
FROM QueryThatWorks
WHERE (((QueryThatWorks.Field4)<900000));


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[platypus71]

Duh.
Thanks.