query results return incomplete text

[dcarley]

The Query contains a memo field, when the query is run it does not provide
all the text in the memo field

[skullaria]

Is it being truncated? I'm no expert, but either you need to move the colum
over to give it more room, or look at design view and see how many characters
you are allowing the field to have. You may not be allowing enough?

[Marshall Barton]

dcarley wrote:

>The Query contains a memo field, when the query is run it does not provide
>all the text in the memo field

A common situation when the query does anything that causes
the memo field to be compared. DISTINCT, ORDER BY, GROUP
BY, UNION, etc.

There are various ways around this issue depending on what
the query is doing.

--
Marsh

[John Spencer (MVP)]

Several possibilities. Access will truncate memo fields to 255 characters when
it needs to do so for ordering purposes and sometimes for comparison purposes.

Access will truncate memo fields when

using a UNION query;
Distinct or DistinctRow;
Aggregate queries (except with First or Last on the Memo field);
When a format has been applied to the memo field;

There are probably others also. First step is to post the SQL text of your query.

[John Vinson]

If you are Sorting or Grouping by the memo field it will be truncated
at 255 bytes. Solution: don't DO that. <g>

Please post the SQL view of the query if you need more help.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

[Jamie Collins]

Suspected causes of truncation of Memo value investigated (comments,
please).

Test using:
MS Access 2000, Jet 4.0 format .mdb
Table and data:

CREATE TABLE Test1 (
MyMemoCol MEMO NOT NULL
)
;
INSERT INTO Test1 (MyMemoCol) VALUES
('A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789A123456789')
;
INSERT INTO Test1 (MyMemoCol) VALUES
('B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789B123456789')
;

Suspect 1: UNION
Test:

SELECT MIN(LEN(DT1.MyMemoCol)) AS min_len
FROM (
SELECT MyMemoCol FROM Test1
UNION ALL
SELECT MyMemoCol FROM Test1
) AS DT1;

Result: min_len = 300.
Conclusion: no truncation.

Suspect 2: DISTINCT
Test:

SELECT DISTINCT MyMemoCol FROM Test1;

Result: Jet engine error: 'The field is too small to accept the amount
of data you attempted to add'.
Conclusion: cannot apply therefore cannot cause truncation.

Suspect 3: GROUP BY
Test:

SELECT MIN(LEN(DT1.MyMemoCol)) as min_len
FROM (
SELECT MyMemoCol FROM Test1
GROUP BY MyMemoCol
) AS DT1;

Result: min_len = 255.
Conclusion: truncated.

Suspect 4: ORDER BY
Test:

SELECT MIN(LEN(DT1.MyMemoCol)) as min_len
FROM (
SELECT MyMemoCol FROM Test1
ORDER BY MyMemoCol
) AS DT1;

Result: min_len = 300.
Conclusion: no truncation.

Suspect 5: Set functions ('aggregate functions') other than FIRST/LAST
Test:

SELECT MAX(MyMemoCol) FROM Test1

Result: Jet engine error: 'Cannot have Memo, OLE, or Hyperlink Object
fields in aggregate argument'.
Conclusion: cannot apply therefore cannot cause truncation.

Jamie.

--

[Marshall Barton]

I never used A2K and don't have time to double check all
that on other versions today, maybe over the weekend. In
the meantime, some commentsL

The message you got for the DISTINCT case is a new one on
me. Seems like they tried to truncate it, but got
confused!?

I didn't expect UNION ALL to get involved with this stuff.
You should change that case to use just UNION.

How about adding another case that uses DISTINCTROW?
--
Marsh
MVP [MS Access]

[Jamie Collins]

Marshall Barton <marsh...@wowway.com> wrote ...

> I never used A2K and don't have time to double check all
> that on other versions today

I'll wager the results would be the same for versions later than
Access2000 as Jet 4.0 is used.

> You should change that case to use just UNION.
> How about adding another case that uses DISTINCTROW?

Suspect 6: DISTINCTROW
Test:

SELECT MIN(LEN(DT1.MyMemoCol)) AS min_len
FROM (

SELECT DISTINCTROW MyMemoCol FROM Test1
) AS DT1;

Result: min_len = 300.
Conclusion: no truncation.

Suspect 7:UNION (rather than UNION ALL)
Test:

SELECT MIN(LEN(DT1.MyMemoCol)) AS min_len
FROM (
SELECT MyMemoCol FROM Test1
UNION

SELECT MyMemoCol FROM Test1
) AS DT1;

Result: min_len = 255.
Conclusion: truncated.

Jamie.

--

[Marshall Barton]

Ok, Jamie, I give up '-)

I couldn't find (didn't keep?) my original tests on this
stuff, so I slapped something together and ran it in A97 and
AXP. The big difference was that AXP allows a memo in the
ORDER BY clause, but A97 doesn't allow it. Other than that,
different error messages and a couple of crashes, the
results were essentially the same as yours.

Bottom line, truncation occurs for UNION and GROUP BY in
Jet4.

--
Marsh
MVP [MS Access]