Memo field values cannot be sorted or grouped. Select Distinct is converting
the mem field values to Text(255) so it can group them instead of just
failing. This is all quite reasonable since a Memo value is of essentially
unlimited size, and the logic to be able to effectively check for duplicates
would be very complicated.
Usually, I find that when someone is trying to group by or use DISTICT with a
Memo field, that the Memo field data is not part of what needs to be evaluates
to check for duplicates. For instance, it might be looked up from a related
table such that it will be the same for any unique combination of the records
being grouped. In this case, you can use a Group By or Distinct query of the
other data including the foreign key to get the related record that has the
Memo, then make another query that joins the first query to the other table.
On Thu, 29 Apr 2004 15:35:05 +0100, skinnybloke <th**************@yahoo.co.uk>
wrote:
Hi - I have a problem with a memo field being truncated to about 255
characters when running a Access 2002 query.
This only seems to happen if I use SELECT DISTINCT. It works ok using
SELECT by itself.
does anyone know why this happens and how to stop it?