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

• 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. 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 and so forth 

MORE INFORMATION

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.

NOTE: A demonstration of the technique used in this article can also be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

Q182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center

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] field 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.

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

   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
Issue type : kbhowto