Microsoft KB Archive/105510

{|
 * width="100%"|

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 </li> Run the query. Note that you are prompted to enter a parameter value. If you remove the sorting, the query will run correctly.</li></ol>
 * }

-

"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." '' ©1997 Microsoft Corporation. All rights reserved. Legal Notices.

''

Additional reference words: 1.00 1.10 2.00 7.00

KBCategory: kbusage

KBSubcategory: QryOthr