By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Using CStr on a Memo field

P: n/a
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?

Oct 22 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
mi********@gmail.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


Oct 22 '06 #2

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

mi********@gmail.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.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 22 '06 #3

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:yY******************@newssvr25.news.prodigy.n et:
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/
Oct 22 '06 #4

P: n/a
"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:
"Rick Brandt" <ri*********@hotmail.comwrote in
news:yY******************@newssvr25.news.prodigy.n et:
>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

Oct 23 '06 #5

P: n/a
David W. Fenton wrote:
"Rick Brandt" <ri*********@hotmail.comwrote in
news:yY******************@newssvr25.news.prodigy.n et:
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

Oct 23 '06 #6

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:zx**************@newssvr11.news.prodigy.com:
David W. Fenton wrote:
>"Rick Brandt" <ri*********@hotmail.comwrote in
news:yY******************@newssvr25.news.prodigy. 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.
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/
Oct 23 '06 #7

P: n/a
David W. Fenton wrote:
"Rick Brandt" <ri*********@hotmail.comwrote in
news:zx**************@newssvr11.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

Oct 24 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.