ACC: Error Using OLE or Memo Field in Union Query (120908)
The information in this article applies to:
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
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 <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.
| Modification Type: | Major | Last Reviewed: | 5/6/2003 |
|---|
| Keywords: | kberrmsg kbprb kbusage KB120908 |
|---|
|