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?

 

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