PRB: Field Aliases in Queries Only Allowed in Select Clause

Article ID: Q105510
Creation Date: 20-OCT-1993
Revision Date: 19-SEP-1996

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

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

A query that sorts on a nested expression in the query will prompt you for a parameter value, and may not sort correctly.

CAUSE

By design, this type of query operation will not run correctly.

RESOLUTION

Remove the nested expression by creating two queries instead of one. This technique enables you to display the same column twice, once unsorted, and the second time, sorted. This example illustrates the workaround:

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

Create a new query based on the Employees table, as follows:

      Query: Query1
      ----------------------
      Type: Select Query
      Table: Employees
      Field: c: [EmployeeID]

      NOTE: In versions 1.x and 2.0, type a space in [Employee ID].

Save the query as Query1

Create a new query based on Query1 as follows:

      Query: Query2
      -----------------------
      Type: Select Query
      Table: Query1
      Field: d: [C]
      Sort: Acending

Run Query2.

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb.

Create a new query based on the Employees table, as follows:

      Query: MySort
      -----------------------
      Type: Select Query
      Table: Employees
      Field: c: [Employee ID]
      Field: d: [c]
         Sort: Ascending

Run the query. Note that you are prompted to enter a parameter value. If you remove the sorting, the query will run correctly.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.