ACC2000: Sorting a Query or Stored Procedure by Using Only a Portion of a Field's Value

ID: Q209821

The information in this article applies to:
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).


SUMMARY

You can sort or group a query or stored procedure by using only a portion of a field's value. 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 might be a part number field that contained the following values:

A-453-34567
A-123-45675
B-234-75658
B-645-65759


MORE INFORMATION

In a Microsoft Access Database (.mdb)

To demonstrate how to sort or group a query by using only a portion of a field's value, this article uses the sample database Northwind.mdb to create a query based on the Employees table. The query uses the Mid() function to sort by six characters beginning with the fourth character in the LastName field. You could also use the Left() and Right() functions to return characters from the left or from the right side of a string argument. 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.


  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:


    4. Sort String: Mid([LastName],4,6)
  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. 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.


If you wanted to use this technique on the part number example

A-453-34567
A-123-45675
B-234-75658
B-645-65759
you could sort these part numbers by the third through fifth characters only by using the following expression:
Expr1: Mid([PartNo]),3,3)

In a Microsoft Access Project (.adp)

To demonstrate how to sort or group a stored procedure by using only a portion of a field's value, this article uses the sample project NorthwindCS.adp to create a stored procedure based on the Employees table. The stored procedure uses the SubString() function to sort by six characters beginning with the fourth character in the LastName field. You could also use the Left() and Right() functions to return characters from the left or from the right side of a string argument.

  1. Open the sample project NorthwindCS.adp.


  2. Create the following stored procedure:


    3. 
CREATE PROCEDURE "SortString_Proc"

AS

   SELECT SUBSTRING(LastName,4,6) AS [Sort String], LastName
   FROM Employees
   ORDER BY (SUBSTRING(LastName,4,6))
  3. Save the stored procedure and switch 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.



REFERENCES

For more information about string expressions, click Microsoft Access Help on the Help menu, type "examples of expressions" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: 


Keywords          : kbdta QryHowto 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: June 10, 1999