ACC: Sorting/Grouping a Query Using a Portion of Field's Value

Last reviewed: April 2, 1997
Article ID: Q88927
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

You can sort or group a query by using only a portion of a field's value. For example, in the Northwind sample database, you can create a query based on the Employees table that uses the Mid() function to sort by six characters beginning with the fourth character in the LastName field. You can use the Left() and Right() functions to return the first and last characters of a string argument.

NOTE: In Microsoft Access versions 1.x and 2.0, there is a space in the Last Name field.

MORE INFORMATION

The following procedure explains how to use the Mid() function to sort data by using only a portion of a field's value:

  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.

  3. Enter the following line in the Field row in the first column of the query grid:

          Sort String:Mid([LastName],4,6)
    

    NOTE: In versions 1.x and 2.0, there is a space in [Last Name].

  4. Select the Sort cell in the Sort String column and select Ascending or Descending.

  5. Drag the LastName field from the Employees box to the QBE grid.

  6. Click the Totals button on the toolbar. Note that Group By now appears in the Totals row of the query grid.

  7. Switch the query to Datasheet view. Note that Sort String appears as the name of the first column, but you can change this header to any other name. Note also that the employee records are sorted by the fourth letters of the employees' last names.

This technique is useful if a field contains values with a set number of leading or trailing characters that are insignificant to your sorting task. An example of this is a part number field called PartNo that contains the following values:

   PartNo
   ------------------------
   A-453-34567
   A-123-45675
   B-234-75658
   B-645-65759 and so forth

To sort these part numbers by the third through fifth characters only, use the following expression:

   Expr:MID([PartNo]),3,3)

REFERENCES

For more information about string functions and related functions, search the Help Index for "String function," "Left function," "Right function," and "Len function."


Keywords : kbusage QryOthr
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


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.

Last reviewed: April 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.