ACC: Error Using OLE or Memo Field in Union Query

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run a SQL-specific union query that contains an OLE Object field, you may receive the following error message:

   Can't use Memo or OLE object field <fieldname> in the SELECT clause of
   a union query.

CAUSE

By default, union queries implicitly sort the data and delete duplicate records. Because Memo and OLE Object fields cannot be sorted, the error occurs.

RESOLUTION

To avoid this error, add the ALL predicate to eliminate the sorting of the field data. For example, add the ALL predicate to the following SQL statement

   SELECT DISTINCTROW Employees.[LastName], Employees.Photo
   FROM Employees
   UNION
   SELECT DISTINCTROW Employees.[LastName], Employees.Photo
   FROM Employees;

   SELECT DISTINCTROW Employees.[LastName], Employees.Photo
   FROM Employees;
   UNION ALL
   SELECT DISTINCTROW Employees.[LastName], Employees.Photo
   FROM Employees;

NOTE: The [LastName] field in the above example should be [Last Name] in version 2.0.

REFERENCES

For more information about union queries, search the Help Index for "union queries," or ask the Microsoft Access 97 Office Assistant.