Microsoft KB Archive/208926

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

Article ID: 208926

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208926



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

This article applies only to a Microsoft Access database (.mdb).



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

Cannot use Memo, OLE, or Hyperlink Object field ' ' in the SELECT clause of a union query.

You observe this behavior when the Access database uses Microsoft Jet 4.0 Service Pack 5 or earlier.



CAUSE
By default, union queries implicitly sort the data and delete duplicate records. Because Memo, OLE, and Hyperlink 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.

