Can't Long text field be sorted?

Question

I wanted to sort my [Subject] field that a Long Text data type. However, the options for sorting are grayed out. I find out that only those fields that have Long text data type are grayed out; I can sort the rest of fields in my Access table.

I made some research and saw that MS only allow to sort up to 255 characters as in Short Text fields.  My question is there is a way to sort a long text field, or I can't do it?

Thank you for your help. 

 

Answer 1

Not in a table's datasheet, but as rows should be ordered in a query, where it is possible to order by a long text column, and not in a table's datasheet, that's no disadvantageous.

---

Ken Sheridan, Stafford, England

Answer 2

In a query, not a table datasheet, you can sort on a long text field; however, only the first 255 characters of the field will be sorted, as though it were a short text field.  I think this is to prevent unlimited consumption of resources.

If you need to sort by the entire field, and your long text field is not all that long, you can (in a query) create calculated fields for each 255-character segment of the field, and sort on them.  For example, a query with SQL like this:

SELECT ID, SomeField, LongTextField FROM Table1
ORDER BY Left(LongTextField,255), Mid(LongTextField, 256, 255)

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Thank you, Ken and Dirk as both of you show me the same direction and it makes sense to me. Dirk shows me more details about how to deal with segments that exceed 255 characters.  I have a very old database that have been upgraded to newer version of Access. As a result, those custom toolbars are automatically attached to Add-Ins ribbon and the table that has the long text datatype was designed as datasheet that I think I can't convert it to a query.    

Answer 4

I'm not sure about the custom toolbars you mention, but working with a datasheet, you *can* use the approach I suggested by choosing the "Advanced Filter/Sort..." option that is in the dropdown list of the "Advanced" button in the "Sort & Filter" group on the ribbon.  That will give you a grid very like the query design grid, in which you can design the calculated fields -- substrings of the Long Text field -- to be sorted.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Thanks, Dirk for explaining.  I will have to post new thread for a custom bar issue with the long text sorting.