ACC: Error Using OLE or Memo Field in Union Query

ID: Q120908

The information in this article applies to:

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;

to produce the statement:


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

Note that the SQL statement with the ALL predicate does not remove duplicate records.

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.

Additional query words: querying

Keywords : kberrmsg kbusage QryUnion
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb