ACC1x: How to Sort on Different Fields Dynamically

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article demonstrates how to design a filter that you can use to dynamically select the field on which to sort records.

MORE INFORMATION

The example below uses the Choose() function in a filter to sort the records displayed on a form. Toggle buttons in an option group are used to sort on different fields. The Choose() function accepts the value of the option group as its first argument, and selects the field from the subsequent list based on that value.

The following example describes how to create a form and a filter that demonstrate this technique:

1. Open the sample database NWIND.MDB.

2. Create the following new form:

   Form: Customer Filter Test --------------------------------

             Caption: Customer Filter Test
             RecordSource: Customers
          Text Box: Company Name
             ControlSource: Company Name
          Text Box: Contact Name
             ControlSource: Contact Name
          Text Box: Address
             ControlSource: Address
   
   

3. Create the following option group on the form:

   Option Group: Field Selector

             AfterUpdate: Apply Sort Macro
   
   

4. Place the following toggle buttons in the option group:

   Toggle Button: CompName

             OptionValue: 1
             Caption: Company Name
          Toggle Button: ContName
             OptionValue: 2
             Caption: Contact Name
          Toggle Button: Addr
             OptionValue: 3
             Caption: Address
   
   

5. View the form in Form view.

6. Choose Edit Filter/Sort from the Records menu.

7. Create the following calculated field:

   NOTE: In the following example, an underscore (_) is used as a line- continuation character. Remove the underscore when re-creating this example.

   Field: Choose(Forms![Customer Filter Test]![Field Selector], _

             [Company Name], [Contact Name], [Address])
          Sort: Ascending
          ' The order of the fields in the Choose() function must match the
          ' order of the OptionValue of the toggle buttons
   
   

8. From the File menu, choose Save As Query. Save the filter as Customer Sort Filter.

9. Save and close the form and close the filter.

10. Create the following new macro and save it as Apply Sort Macro:

    Apply Sort Macro Actions ------------------------------------ Action: ApplyFilter

              Filter Name: Customer Sort Filter
    
    

11. Open the Customer Filter Test form. Click the Contact Name toggle button.

REFERENCES

Microsoft Access "Language Reference," version 1.0, "Choose Function," page 65

For more information on the Choose() function, search for "Choose" using the Microsoft Access Help menu.