Using CStr on a Memo field

[midlothian]

Does using CStr on a memo field truncate it to 255 characters? Seems
like this is happening with some data in my tables. Is there a way
around this?

[Rick Brandt]

mi********@gmai l.com wrote:

Does using CStr on a memo field truncate it to 255 characters? Seems
like this is happening with some data in my tables. Is there a way
around this?

No. There are numerous things that WILL truncate a memo field to 255
characters, but CStr() is not one of them. Some that will...

Grouping or sorting on it in a query
Grouping or sorting on it in a report
Using DISTINCT (Unioque Values) in a query
Applying a format property in a table or query field or in a Report or form
control

I'm sure there are more.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[ruralguy via AccessMonster.com]

Rick has given you his usually excellent answer. I'm just curious as to why
you are using CStr() on a text field?

mi********@gmai l.com wrote:

>Does using CStr on a memo field truncate it to 255 characters? Seems
like this is happening with some data in my tables. Is there a way
around this?

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[David W. Fenton]

"Rick Brandt" <ri*********@ho tmail.comwrote in
news:yY******** **********@news svr25.news.prod igy.net:

There are numerous things that WILL truncate a memo field to 255
characters, but CStr() is not one of them. Some that will...

Grouping or sorting on it in a query
Grouping or sorting on it in a report
Using DISTINCT (Unioque Values) in a query
Applying a format property in a table or query field or in a
Report or form control

I'm sure there are more.

Er, none of those that I know of will truncate the field. Indeed, if
you try to group or sort or use the DISTINCT operator on a memo
field, it will throw an error.

I've only ever used Left() to truncate memo fields for those
purposes. i.e., to do a sort/group/DISTINCT on a memo when it was
unfortunately required.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

"David W. Fenton" <XX*******@dfen ton.com.invalid wrote in
news:Xn******** *************** ***********@127 .0.0.1:

"Rick Brandt" <ri*********@ho tmail.comwrote in
news:yY******** **********@news svr25.news.prod igy.net:

>There are numerous things that WILL truncate a memo field to
255 characters, but CStr() is not one of them. Some that
will...

Grouping or sorting on it in a query
Grouping or sorting on it in a report
Using DISTINCT (Unioque Values) in a query
Applying a format property in a table or query field or in a
Report or form control

I'm sure there are more.

Er, none of those that I know of will truncate the field.
Indeed, if you try to group or sort or use the DISTINCT
operator on a memo field, it will throw an error.

You've obviously not used the dumbed-down recent releases of
Access, which surrepticiously apply the Left(memo,255) without
notification. '97 is the last version not broken by this
stupidity.

I've only ever used Left() to truncate memo fields for those
purposes. i.e., to do a sort/group/DISTINCT on a memo when it
was unfortunately required.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Rick Brandt]

David W. Fenton wrote:

"Rick Brandt" <ri*********@ho tmail.comwrote in
news:yY******** **********@news svr25.news.prod igy.net:

There are numerous things that WILL truncate a memo field to 255
characters, but CStr() is not one of them. Some that will...

Grouping or sorting on it in a query
Grouping or sorting on it in a report
Using DISTINCT (Unioque Values) in a query
Applying a format property in a table or query field or in a
Report or form control

I'm sure there are more.

Er, none of those that I know of will truncate the field. Indeed, if
you try to group or sort or use the DISTINCT operator on a memo
field, it will throw an error.

I've only ever used Left() to truncate memo fields for those
purposes. i.e., to do a sort/group/DISTINCT on a memo when it was
unfortunately required.

That is true (in Access 97 and older). However; in those versions one could do
all of those things against the first 255 characters of a memo field by using
Left([Field], 255).

In their infinite wisdom MS decided to modify all of the newer versions so that
instead of telling you that those things aren't allowed with memo fields that
just internally truncate all but the first 255 characters automatically. That
decision surely ranks in the top ten as far as the newsgroup questions
generated.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

"Rick Brandt" <ri*********@ho tmail.comwrote in
news:zx******** ******@newssvr1 1.news.prodigy. com:

David W. Fenton wrote:

>"Rick Brandt" <ri*********@ho tmail.comwrote in
news:yY******* ***********@new ssvr25.news.pro digy.net:

There are numerous things that WILL truncate a memo field to
255 characters, but CStr() is not one of them. Some that
will...

Grouping or sorting on it in a query
Grouping or sorting on it in a report
Using DISTINCT (Unioque Values) in a query
Applying a format property in a table or query field or in a
Report or form control

I'm sure there are more.

Er, none of those that I know of will truncate the field. Indeed,
if you try to group or sort or use the DISTINCT operator on a
memo field, it will throw an error.

I've only ever used Left() to truncate memo fields for those
purposes. i.e., to do a sort/group/DISTINCT on a memo when it was
unfortunatel y required.

That is true (in Access 97 and older). However; in those versions
one could do all of those things against the first 255 characters
of a memo field by using Left([Field], 255).

In their infinite wisdom MS decided to modify all of the newer
versions so that instead of telling you that those things aren't
allowed with memo fields that just internally truncate all but the
first 255 characters automatically. That decision surely ranks in
the top ten as far as the newsgroup questions generated.

Does it? I've never seen anything about this automatic truncation,
but I guess I don't read all the posts.

Surely, newer versions of Access will respect, say, Left(MemoField,
4096), and not truncate?

Of course, any time you're sorting or grouping on memo fields,
you've probably got a design error, so I'm not sure this is a
terrible thing.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Rick Brandt]

David W. Fenton wrote:

"Rick Brandt" <ri*********@ho tmail.comwrote in
news:zx******** ******@newssvr1 1.news.prodigy. com:

>That is true (in Access 97 and older). However; in those versions
one could do all of those things against the first 255 characters
of a memo field by using Left([Field], 255).

In their infinite wisdom MS decided to modify all of the newer
versions so that instead of telling you that those things aren't
allowed with memo fields that just internally truncate all but the
first 255 characters automatically. That decision surely ranks in
the top ten as far as the newsgroup questions generated.

Does it? I've never seen anything about this automatic truncation,
but I guess I don't read all the posts.

It comes up in the reports group all the time where people are only seeing
the first 255 characters of a memo field in their report output. Also where
formats were applied in forms or when exporting the data.

Surely, newer versions of Access will respect, say, Left(MemoField,
4096), and not truncate?

They would not truncate on that expression alone, but if you did one of the
"illegal" things with the output of that expression it would likely still
truncate it to 255.

Of course, any time you're sorting or grouping on memo fields,
you've probably got a design error, so I'm not sure this is a
terrible thing.

Exactly my point. When you did one of these things in older versions you
were given an error to clue you into the fact that you were likely going
astray. The newer versions just assume that allowing the operation while
truncating is "probably what the user really wants".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com