Concatenation Limit in Calculated Query Field

[Jay]

Hi all -

I'm trying to concatenate strings in a calculated query field. Although
Access accepts the formula, the length of the calculated field is limited to
about 254-256 characters (I keep losing count, but it's right in the magical
256 character neighborhood).

The following calculated field "Cat" demonstrates the formula. It
concatenates two short text fields with a memo field ('Desription').
Actually, I'd like to concatenate the first 300 characters of the memo field.
Any advice on whether the limit can be lifted or why this happens would be
appreciated.

Cat: [Source] & " : " & [Category] & " : " & [SubCategory] &
IIf(IsNull(Trim([Description])),""," (" & Left([Description],300) & ")")

 

[Marshall Barton]

I'm trying to concatenate strings in a calculated query field. Although
Access accepts the formula, the length of the calculated field is limited to
about 254-256 characters (I keep losing count, but it's right in the magical
256 character neighborhood).

The following calculated field "Cat" demonstrates the formula. It
concatenates two short text fields with a memo field ('Desription').
Actually, I'd like to concatenate the first 300 characters of the memo field.
Any advice on whether the limit can be lifted or why this happens would be
appreciated.

Cat: [Source] & " : " & [Category] & " : " & [SubCategory] &
IIf(IsNull(Trim([Description])),""," (" & Left([Description],300) & ")")

That's normally a consequence of using a query feature that
rquires the field to be compared. Common causes are the
DISTINCT, GROUP BY, ORDER BY, UNION.

Specifying a format for such a value will also truncate a
long text value.

 

[Jay]

Hi Marshall -

Thanks for the information. I'm using the formula in a GroupBy clause so
you're right on target. It makes sense, I suppose, for the value of a memo
field (or other long field) to be truncated in the GroupBy context; otherwise
performance might suffer if many comparisons are made among very large memo
fields.

Your information has saved me from diddling endlessly for a solution. I'll
construct a work-around and will re-post if the workaround is solid.

Thanks again,
Jay