query results return incomplete text

84 views
Skip to first unread message

dcarley

unread,
Nov 16, 2004, 5:10:07 PM11/16/04
to
The Query contains a memo field, when the query is run it does not provide
all the text in the memo field

skullaria

unread,
Nov 16, 2004, 6:11:03 PM11/16/04
to
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

unread,
Nov 16, 2004, 7:36:53 PM11/16/04
to
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)

unread,
Nov 16, 2004, 7:51:03 PM11/16/04
to
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

unread,
Nov 16, 2004, 8:46:20 PM11/16/04
to

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

unread,
Nov 19, 2004, 4:45:33 AM11/19/04
to
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

unread,
Nov 19, 2004, 8:08:27 AM11/19/04
to
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

unread,
Nov 22, 2004, 3:15:07 AM11/22/04
to
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

unread,
Nov 28, 2004, 9:51:53 AM11/28/04
to
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]

Reply all
Reply to author
Forward
0 new messages