how do I sort on memo fields in access 2007?

[viritrilbia]

In Access 2003, I could sort on memo fields. But in Access 2007, when I'm in
a memo field, the sort buttons are disabled. Is there anything I can do
about this?

 

[Armen Stein]

In Access 2003, I could sort on memo fields. But in Access 2007, when I'm in
a memo field, the sort buttons are disabled. Is there anything I can do
about this?

Sorting on memo fields isn't a good idea in the first place, because
they are stored in a way that makes them perform poorly, and they can
hold a lot of text, which usually makes sorting not make much sense.

Are you sure you can't store your data in a text field instead?

If you really need to sort by the memo field, try this. In a query,
create a new calculated column like this:

SortableMemo: Left(MyMemoField, 255)

Where MyMemoField is the name of your memo field. Then sort by that
calculated field. It will sort on the first few sentences of text.
You'll still have some peformance issues for large numbers of records,
and it still might not make sense to do it, but it will work.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[viritrilbia]

Thanks for the quick reply! I infer from your answer that this removal of
functionality was a conscious, though apparently undocumented, decision?

Are you sure you can't store your data in a text field instead?

My data is actually stored in a postgresql database to which I connect via
ODBC. There it is stored in fields with the postgresql data type "text". I
believe the Postgresql ODBC driver can be configured to import these fields
as either Text or Memo, but the problem is that some of them contain long
entries, so that if I import them all as Text then they get truncated.

I could, in theory, change the data types of the postgres fields that I want
to be sortable to be say varchar(255) which I guess would then get imported
as Text and be sortable, but that would involve a significant amount of work,
since there are a lot of those fields.

Thanks for the workaround, anyway.