Microsoft KB Archive/120908

= ACC: Error Using OLE or Memo Field in Union Query =

Article ID: 120908

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q120908



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



SYMPTOMS
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 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.

