Sorting memo field - Access 2007

[Justin]

Everything that I've read suggests that you can sort a memo field, however, I
am unable to do so, specifically in a table and in trying to create a report.
Can someone tell me why or how to fix this problem? My concern is that in
order to take advantage of the rich text formatting, you must use a memo
field, even if you don't necessarily need the length of text provided by this
field; but if you can't group or sort it and if the memo field should still
only be used as a 'Notes' field, as so many people suggest, then what's the
point of having the option to use the rich text? Why would you need rich
text in a 'Notes' field?

 

[Rick Brandt]

Everything that I've read suggests that you can sort a memo field,
however, I am unable to do so, specifically in a table and in trying
to create a report. Can someone tell me why or how to fix this
problem? My concern is that in order to take advantage of the rich
text formatting, you must use a memo field, even if you don't
necessarily need the length of text provided by this field; but if
you can't group or sort it and if the memo field should still only be
used as a 'Notes' field, as so many people suggest, then what's the
point of having the option to use the rich text? Why would you need
rich text in a 'Notes' field?

Why would you need rich text in any field?

There is a function that will extract text (non-formatted) from your rich-text
enabled field. Use that in a query and sort on the results.

 

[Justin]

Why would you need rich text in any field?

There is a function that will extract text (non-formatted) from your rich-text
enabled field. Use that in a query and sort on the results.

I work in the orchestra world, and it is very handy to be able to use the
industry standard on the titles of compositions, all or parts of which are
italicized, all or parts of which are in quotes and all or parts of which are
plain, all depending on the type of piece and descriptiveness of the title.

I actually have two memo fields, one in rich text and one in plain, and I
can't sort or group on either one.

 

[Dirk Goldgar]

In

I work in the orchestra world, and it is very handy to be able to use
the industry standard on the titles of compositions, all or parts of
which are italicized, all or parts of which are in quotes and all or
parts of which are plain, all depending on the type of piece and
descriptiveness of the title.

I actually have two memo fields, one in rich text and one in plain,
and I can't sort or group on either one.

I believe you can sort a memo field only on the first 255 characters of
the field, but with that stipulation it ought to work. I wouldn't
expect you to be able to sort properly on the rich text field, because
then you'd probably end up including the RTF formatting codes in the
sort.

Grouping on a memo field will truncate it to 255 characters.

When you say you can't sort or group on your memo field, what exactly do
you mean? What are you seeing?

 

[Justin]

I believe you can sort a memo field only on the first 255 characters of
the field, but with that stipulation it ought to work. I wouldn't
expect you to be able to sort properly on the rich text field, because
then you'd probably end up including the RTF formatting codes in the
sort.

Grouping on a memo field will truncate it to 255 characters.

When you say you can't sort or group on your memo field, what exactly do
you mean? What are you seeing?

In a table, when I highlight the memo field that is plain text, the sort
button in the tool bar goes gray, and I cannot sort, even truncated (same
with the field with rtf). If I try to create a report using the report
wizard, it will not allow me to group or sort on either of the memo fields -
neither appear in the list.

If it can't be done, then I'll just work around it. I just thought it was
odd that nothing I've read about 2007 specifically states 'you cannot sort or
group on a memo field in a table or report' and I've read a great deal that
suggests what you said, that it should, it will just be truncated.

Thank you for your help.

 

[Rick Brandt]

In a table, when I highlight the memo field that is plain text, the
sort button in the tool bar goes gray, and I cannot sort, even
truncated (same with the field with rtf). If I try to create a
report using the report wizard, it will not allow me to group or sort
on either of the memo fields - neither appear in the list.

If it can't be done, then I'll just work around it. I just thought
it was odd that nothing I've read about 2007 specifically states 'you
cannot sort or group on a memo field in a table or report' and I've
read a great deal that suggests what you said, that it should, it
will just be truncated.

Thank you for your help.

Sorting in a table is a meaningless activity. We were all talking about
sorting in forms, reports, and queries.

 

[Dirk Goldgar]

In

In a table, when I highlight the memo field that is plain text, the
sort button in the tool bar goes gray, and I cannot sort, even
truncated (same with the field with rtf). If I try to create a
report using the report wizard, it will not allow me to group or sort
on either of the memo fields - neither appear in the list.

That's odd. It doesn't work (or "fail to work" that way in Access
versions from 97 to 2002, and I bet it's not like that for Access 2003,
either. I can't confirm your experience with Access 2007, as I don't
have that installed. Maybe they decided that, since sorting and
grouping is limited on memo fields, they would just remove that
functionality from the user interface.

What format is your database in? MDB or ACCDB?

If it can't be done, then I'll just work around it. I just thought
it was odd that nothing I've read about 2007 specifically states 'you
cannot sort or group on a memo field in a table or report' and I've
read a great deal that suggests what you said, that it should, it
will just be truncated.

Can you create a query that sorts or groups on your memo field?
Assuming you can, you can probably do whatever you need to do with that.

 

[Pieter Wijnen]

A Trick may be to Sort on a substring of the Memo , I believe I did that
once in Access 2.0 ...

ie

SELECT MyMemo, Left(MyMemo,64) As Sort
From MyTable
ORDER BY 2

HTH

Pieter