ACC2000: Error Using OLE or Memo Field in Union Query

ID: Q208926

The information in this article applies to:

Microsoft Access 2000

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 '<fieldname>' in the SELECT clause of a union query.

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.

REFERENCES

For more information about union queries, click Microsoft Access Help on the Help menu, type "What is an SQL query and when would you use one" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: prb querying


Keywords          : kberrmsg kbdta QryUnion 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999