Truncating SQL Server "Text" field

[JimP]

I have aquery that appends data from a "Text" field in SQl Server to a
"memo" field in a MS-Access table. However, only the first 255 characters
get appended.

How can I get all chars, including those beyond 255?

Using the same SQL Server "text" field in a report, does print all chars
(even those beyond 255)

 

[JimP]

Please disregard - it turns out this is some type of formatting issue on the
report. The data is in the report query - but the field is not printing all
of the characters - even though "can grow" is set to "yes'

 

[John W. Vinson]

Please disregard - it turns out this is some type of formatting issue on the
report. The data is in the report query - but the field is not printing all
of the characters - even though "can grow" is set to "yes'

This will happen under several circumstances: if you have any Format
specification on the memo field textbox; if you try to sort or group by the
field; if you use a UNION query (as distinct from UNION ALL); various others.
Post back with more specifics if you need this capability.

 

[David W. Fenton]

This will happen under several circumstances: if you have any
Format specification on the memo field textbox; if you try to sort
or group by the field; if you use a UNION query (as distinct from
UNION ALL); various others. Post back with more specifics if you
need this capability.

Back before Jet truncated for you (which I consider a bad feature,
as it tends to bite more people than the old way), I always used
something like Left(MemoField, 4096) so I could sort on them.
Indeed, I once wrote a function, varTruncateMemo, that accepted the
memo value and an optional number for the length you wanted to
return (I think it default to 1024), and returned the requested memo
field truncated to that length. Of course, I hardly ever had memo
fields of such length, so it always returned the whole memo
contents.

 

[JimP]

Thank you for the suggestions. I am still coming up empty, in spite of
trying numerous things.

There is no sorting or grouping in the report query. I can take the report
query and put this single field in a new report and it prints fine.

I've tried a Dlookup on the native SQL Server table (in the report - outside
of the report query)and at one point it did get the contents of the whole
field - now it doesn't - don't know what's at play.

 

[David W. Fenton]

I've tried a Dlookup on the native SQL Server table (in the report
- outside of the report query)and at one point it did get the
contents of the whole field - now it doesn't - don't know what's
at play.

Is it a link to a table or to a view? I'm not sure that should make
a difference but it might.

You also might try deleting and recreating the table link, since
changes on the SQL Server often cause the metadata in your table
link to be outdated such that the table behaves strangely (though
it's usually more like becoming uneditable, or missing newly-added
fields).