Microsoft KB Archive/123587

= ACC2: Constant in ORDER BY Clause of Union Query Is Ignored =

Article ID: 123587

Article Last Modified on 6/25/2002

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q123587



Novice: Requires knowledge of the user interface on single-user computers.



SYMPTOMS
When you run a union query containing an ORDER BY clause that references a constant, the constant value is ignored.

If the reference to the constant is the only item in the ORDER BY clause, no ordering of the data is performed. If the ORDER BY clause contains additional items, however, ordering by the other items is performed.



RESOLUTION
Create another query based on the union query, and then sort the data in the new query.



STATUS
Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs with the Microsoft Jet database engine version 2.5, which is available with the Microsoft Access version 2.0 Service Pack. For information about how to obtain the Service Pack, please see the following article in the Microsoft Knowledge Base:

122927 WX1124: Microsoft Access Version 2.0 Service Pack



Steps to Reproduce Problem
 Start Microsoft Access and open the sample database NWIND.MDB.  Create a new union query using the following SQL statement:

     SELECT DISTINCTROW Employees.[Last Name], Employees.Title, "Emp" AS Source FROM Employees UNION SELECT DISTINCTROW Customers.[Contact Name], Customers.[Contact Title], "Cust" FROM Customers ORDER BY [Source];  Run the query. Note that the data is not ordered as you expect.

