cannot sort memo field A - Z

[Moira Baker]

I have had to create a memo field in my database but now cannot sort A - Z.
Can anyone tell me how I can get it to sort or is there any alternative to
memo which WILL let me sort but also give me the extra text space?

 

[Jerry Whittle]

That's one of the little problems with Memo fields. However you can get
around it with something like this in a query:

SortMemo: Left([TheMemoFieldName], 254)

Then sort on this new field. The Left function extracts the first 254
characters which then can be sorted. The SQL would look something like this:

SELECT tblTableName.*
FROM tblTableName
ORDER BY Left([TheMemoFieldName],255);

 

[Ken Snell \(MVP\)]

See Allen Browne's website for detailed information about truncation of memo
fields:
http://allenbrowne.com/ser-63.html

Sorting a memo field shouldn't cause truncation, so I'm wondering if you
have something else in your query that is causing the truncation. Show us
the SQL statement of the query that you're using and which is truncating the
memo field's contents.

 

[Ken Snell \(MVP\)]

Also, Jamie Collins posted the results of various tests for memo field
truncation scenarios. See this post for the results:
http://groups.google.com/group/micr...sults+return+incomplete+text#30e12d6c4c8e5ece

His tests confirmed that ORDER BY (sorting) does not truncate a memo field's
contents.

 

[John W. Vinson]

On Fri, 10 Oct 2008 07:43:01 -0700, Moira Baker <Moira

I have had to create a memo field in my database but now cannot sort A - Z.
Can anyone tell me how I can get it to sort or is there any alternative to
memo which WILL let me sort but also give me the extra text space?

Memos cannot be indexed, so any sort will be very slow at best.

Try creating a query based on the table. Specify Ascending on the memo field.

If that doesn't work, include a calculated field:

Sortkey: Left([memofield], 255)

and sort by that.

 

[Ken Snell \(MVP\)]

Also this URL shows both of Jamie's posts with all 7 of his tests:
http://groups.google.com/group/micr...4e2d1b65a60e7/489970072eafe7a3?hl=en&lnk=st&q

 

[David W. Fenton]

If that doesn't work, include a calculated field:

Sortkey: Left([memofield], 255)

Is it not the case that recent versions of Access do this without
telling you? That is, you are allowed to apply a sort to a memo
field and it silently truncates the result to 255 characters?

If you don't like that (or are using an earlier version of Access),
you can use Left(Memofield,N) where N is any number you want. I am
rather fond of N=4096, myself.

 

[David W. Fenton]

His tests confirmed that ORDER BY (sorting) does not truncate a
memo field's contents.

But his results were not uniform for all scenarios. Marshall Barton
had a nice summary:

Bottom line, truncation occurs for UNION and GROUP BY in
Jet4.

(from
http://groups.google.com/group/microsoft.public.access.queries/msg/03
d29389580b5270)

 

[Ken Snell \(MVP\)]

Yes, truncation occurs for UNION and GROUP BY.... but not ORDER BY.