In Access 2003, problem with a memo field if and only if there is a filter on the PK and a sub query is joined.

Question

I have a problem in a query in Acess 2003 (SP3).

I have a query that includes some tables and a sub query. The sub query and tables are all joined to a main table. The query uses some aggregate functions and there is a HAVING clause that filters the result on the primary key (PK).

Under these conditions, a memo field of the main table is not displayed properly. Two garbage characters, never the same, are displayed instead of the content of the field.

Now what is weird is that if I remove the HAVING clause, or if I use it to filter on something else other than the PK, the field is displayed correctly. If I remove the sub query from the query the field is also displayed correctly even if there is still a filter (HAVING clause) on the PK.

Is this a bug in Access (I think it is)? If so, does someone know of a workaround for this bug?

Answer 1

MSAccess Memo fields truncated to 255 characters (before Access 2000, wouldn't work at all) in GROUP BY queries. However, to take care of the apparent bug try this:

Instead of MemoField  use  Left([MemoField,255)