Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/105510

From BetaArchive Wiki

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:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).
  2. 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].
  3. Save the query as Query1
  4. Create a new query based on Query1 as follows:

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

MORE INFORMATION

Steps to Reproduce Behavior


  1. Open the sample database Northwind.mdb.
  2. 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
  3. 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.

©1997 Microsoft Corporation. All rights reserved. Legal Notices.


Additional reference words: 1.00 1.10 2.00 7.00
KBCategory: kbusage
KBSubcategory: QryOthr