2

Any idea what's wrong with the query 

SELECT DISTINCT Table1.Description
FROM Table1
ORDER BY Table1.Description DESC;

I'm running this on MsAccess on a table where all fields are Memo. There are no indexes, primary or anything like that just a bunch of Memos. I'm getting the error Reserved error (-1060); there is no message for this error.

If you think my query is OK please let me know because I just had a problem with MDAC being corrupted and had to reinstall so it could be related to that.

Edit: OK I narrowed it down so you guys can try to repro:

  1. Create a blank database (table Table1 and ID field will be there by default) in MS Access.
  2. Add a field called Field1 of type Memo
  3. Add some random data and
  4. Try to run this query: SELECT DISTINCT Field1 FROM Table1 ORDER BY Field1

If you SELECT * it will work but won't be uniquer because it will consider the ID field.

If you don't get an error please let me know because that would mean my database engine is messed up.

Improve this question
3
  • 1
    Try and narrow it down: does select 1; work? select * from Table1;?
    – Jack Douglas
    Sep 12, 2011 at 8:39
  • @Jack: please see my edit.
    – Ahmet
    Sep 12, 2011 at 8:51
  • Have you seen this SO answer? It looks like distinct does not play nicely with memo.
    – Jack Douglas
    Sep 12, 2011 at 10:01

1 Answer 1

Reset to default
3

Which MS Access version are you using?

Depending on MS Access version it's not possible to order by memo fields. You will need a workaround like:

ORDER BY Left(Description, 127) DESC
Improve this answer
3
  • Should it work on 2010 version?
    – Ahmet
    Sep 12, 2011 at 9:41
  • ORDER BY should work with 2010, but it's not really sorting on the whole field content. But the problem in your case is the combination with DISTINCT, as Access is truncating internally the memo field to sort on it (like in my above sample). And that conflicts with DISTINCT.
    – MicSim
    Sep 12, 2011 at 10:13
  • Oh I think I got it now. When I use ORDER BY a memo, internally, the memo is not being selected anymore but a truncated version of the memo, and can't do DISTINCT on a field that is not listed after the SELECT statement. Actually for your sample to work I had to also list Left(Description, 127) after the SELECT as one more column.
    – Ahmet
    Sep 13, 2011 at 5:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.