Maximum length of concatenation in Access 2003

[rp10001]

The following query works in Access 2002. The inputs [start] and [end]
and the result [Text] are all memo fields -- the result can certainly
take any reasonable length.

INSERT INTO tbl1 ( [Text] )
SELECT DISTINCT [Start] & Chr$(13) & Chr$(10) & [end] AS [Text]
FROM tbl2;

In Access 2003, the result is truncated to 255 characters. Does anyone
have any ideas how to get round this?

Many thanks
Rachael

[Sorry if this has been answered elsewhere -- I've spent some time
looking, and can't find anything directly relevant.]

 

[Douglas J. Steele]

I'm very surprised that it works in Access 2002.

The inclusion of the DISTINCT keyword is the culprit. Since a memo field
can't be sorted (or indexed), Access truncates at 255 in order to carry out
the work necessary to return only distinct values.

 

[Rick Brandt]

The following query works in Access 2002. The inputs [start] and [end]
and the result [Text] are all memo fields -- the result can certainly
take any reasonable length.

INSERT INTO tbl1 ( [Text] )
SELECT DISTINCT [Start] & Chr$(13) & Chr$(10) & [end] AS [Text]
FROM tbl2;

In Access 2003, the result is truncated to 255 characters. Does anyone
have any ideas how to get round this?

Many thanks
Rachael

[Sorry if this has been answered elsewhere -- I've spent some time
looking, and can't find anything directly relevant.]

Shouldn't work in any version. DISTINCT will always truncate a memo field to
255 characters. Are you sure you had the DISTINCT clause in there when you
tried this in Access 2002?

 

[Jamie Collins]

The following query works in Access 2002. The inputs [start] and [end]
and the result [Text] are all memo fields -- the result can certainly
take any reasonable length.

INSERT INTO tbl1 ( [Text] )
SELECT DISTINCT [Start] & Chr$(13) & Chr$(10) & [end] AS [Text]
FROM tbl2;

In Access 2003, the result is truncated to 255 characters. Does anyone
have any ideas how to get round this?

Shouldn't work in any version. DISTINCT will always truncate a memo fieldto
255 characters.

I can't get it to work at all e.g.

CREATE TABLE Test1 (
MyMemoCol MEMO NOT NULL
)
;
INSERT INTO Test1 (MyMemoCol) VALUES
('A123456789A123456789A123456789A123456789A123456789A123456789A123456789A12­3456789A123456789A123456789A123456789A123456789A123456789A123456789A1234567­89A123456789A123456789A123456789A123456789A123456789A123456789A123456789A12­3456789A123456789A123456789A123456789A123456789A123456789A123456789A1234567­89')

;
INSERT INTO Test1 (MyMemoCol) VALUES
('A123456789A123456789A123456789A123456789A123456789A123456789A123456789A12­3456789A123456789A123456789A123456789A123456789A123456789A123456789A1234567­89A123456789A123456789A123456789A123456789A123456789A123456789A123456789A12­3456789A123456789A123456789A123456789A123456789A123456789A123456789A1234567­8X')

;
SELECT DISTINCT MyMemoCol
FROM Test1
;

The above query generates an error, "The field is too small to accept
the amount of data you attempted to add. Try inserting or pasting less
data."

Jamie.

--

 

[rp10001]

Ah, thank you all. No, I'm not certain I didn't have DISTINCT in there
-- that change may have occurred later, without having any effect for
the particular values of the parameters.

Thanks to all.

Rachael

I'm very surprised that it works in Access 2002.

The inclusion of the DISTINCT keyword is the culprit. Since a memo field
can't be sorted (or indexed), Access truncates at 255 in order to carry out
the work necessary to return only distinct values.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

The following query works in Access 2002. The inputs [start] and [end]
and the result [Text] are all memo fields -- the result can certainly
take any reasonable length.

INSERT INTO tbl1 ( [Text] )
SELECT DISTINCT [Start] & Chr$(13) & Chr$(10) & [end] AS [Text]
FROM tbl2;

In Access 2003, the result is truncated to 255 characters. Does anyone
have any ideas how to get round this?

Many thanks
Rachael

[Sorry if this has been answered elsewhere -- I've spent some time
looking, and can't find anything directly relevant.]

 

[David W. Fenton]

The following query works in Access 2002. The inputs [start] and
[end] and the result [Text] are all memo fields -- the result can
certainly take any reasonable length.

INSERT INTO tbl1 ( [Text] )
SELECT DISTINCT [Start] & Chr$(13) & Chr$(10) & [end] AS [Text]
FROM tbl2;

In Access 2003, the result is truncated to 255 characters. Does
anyone have any ideas how to get round this?

Many thanks
Rachael

[Sorry if this has been answered elsewhere -- I've spent some
time looking, and can't find anything directly relevant.]

Shouldn't work in any version. DISTINCT will always truncate a
memo field to 255 characters.

Er, no -- it just won't work in versions before 2000, giving an
error saying you can't aggregate on memo fields (or something like
that).

I think the truncation at 255 is a really stupid thing, one of those
"guesses" at what you want that does too much.

 

[Jamie Collins]

CREATE TABLE Test1 (
MyMemoCol MEMO NOT NULL
)
;
INSERT INTO Test1 (MyMemoCol) VALUES
('A123456789A123456789A123456789A123456789A123456789A123456789A123456789A12­­3456789A123456789A123456789A123456789A123456789A123456789A123456789A123456­7­89A123456789A123456789A123456789A123456789A123456789A123456789A123456789A­12­3456789A123456789A123456789A123456789A123456789A123456789A123456789A1234­567­89')

;
INSERT INTO Test1 (MyMemoCol) VALUES
('A123456789A123456789A123456789A123456789A123456789A123456789A123456789A12­­3456789A123456789A123456789A123456789A123456789A123456789A123456789A123456­7­89A123456789A123456789A123456789A123456789A123456789A123456789A123456789A­12­3456789A123456789A123456789A123456789A123456789A123456789A123456789A1234­567­8X')

;
SELECT DISTINCT MyMemoCol
FROM Test1
;

The above query generates an error, "The field is too small to accept
the amount of data you attempted to add. Try inserting or pasting less
data."

Just to be clear, the INSERTs work fine. It is the vanilla *SELECT*
query that generates the error about, "Try inserting or pasting less
data." I assume what happens is that under the covers a temp table with
an NVARCHAR(255) column is being created and the error exposed is one
generated as a result of this internal process trying to populate the
temp table

Jamie.

--