query results return incomplete text

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

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?

"dcarley" wrote:

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

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

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.



dcarley wrote:

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

On Tue, 16 Nov 2004 14:10:07 -0800, "dcarley"
wrote:

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

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

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
('A123456789A123456789A123456789A123456789A1234567 89A123456789A123456789A123456789A123456789A1234567 89A123456789A123456789A123456789A123456789A1234567 89A123456789A123456789A123456789A123456789A1234567 89A123456789A123456789A123456789A123456789A1234567 89A123456789A123456789A123456789A123456789A1234567 89')
;
INSERT INTO Test1 (MyMemoCol) VALUES
('B123456789B123456789B123456789B123456789B1234567 89B123456789B123456789B123456789B123456789B1234567 89B123456789B123456789B123456789B123456789B1234567 89B123456789B123456789B123456789B123456789B1234567 89B123456789B123456789B123456789B123456789B1234567 89B123456789B123456789B123456789B123456789B1234567 89')
;

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.

--

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 wrote:

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
('A123456789A123456789A123456789A123456789A123456 789A123456789A123456789A123456789A123456789A123456 789A123456789A123456789A123456789A123456789A123456 789A123456789A123456789A123456789A123456789A123456 789A123456789A123456789A123456789A123456789A123456 789A123456789A123456789A123456789A123456789A123456 789')
;
INSERT INTO Test1 (MyMemoCol) VALUES
('B123456789B123456789B123456789B123456789B123456 789B123456789B123456789B123456789B123456789B123456 789B123456789B123456789B123456789B123456789B123456 789B123456789B123456789B123456789B123456789B123456 789B123456789B123456789B123456789B123456789B123456 789B123456789B123456789B123456789B123456789B123456 789')
;

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 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.

--

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]



Jamie Collins wrote:

Marshall Barton 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.