| Feedback | Assorted Access Queries |

Offering choices of how to sort data in an Access query

UK more...   USA more ...

You may wish to produce a report which displays data sorted in various different ways. For example a list of business addresses may be sorted by either (1) company name (2) location of premises (3) type of business. A single report based a single query can be used and will display a dialog box prompting the user to choose their preferred sort order.

Create a query run time parameter which will prompt for various options.

Query Parameters x
  Parameter Data type  
   Sort by 1 booking 2 inv ... Integer   
  btn btn  

The parameter prompt in full is: Sort by 1 booking 2 invoice 3 paid

In this example, a report of vehicle bookings can be sorted either by (1) the date on which the booking started, (2) the invoice date or (3) the date on which the invoice was paid. The prompt will request an integer value of 1, 2 or 3 to make the choice.

Param-Dates01: Select Query ox
Field: MySort: Choose([Sort by 1 booki... UserGroup_Name Date_out Date_Return Invoice_Date  
Table:   Bookings Bookings Bookings Bookings  
Sort: Ascending          
Show: Y Y Y Y Y  

The full definition for the user defined field is - MySort: Choose([Sort by 1 booking 2 invoice 3 paid], [date_out], [invoice_date], [date_invpaid])

The CHOOSE() function is a useful replacement for IIF() when there are more than two numerical results. It takes the form CHOOSE(OptionNumber, Result_If_Option1, Result_If_Option2, .... Result_If_Option99). Therefore in this example where the user is prompted to enter 1, 2 or 3, there can be three alternative field names as the result.

The MySort field is then available to create a Sorting and Grouping expression in a report.

It is also possible to create a second similar field which describes the choice of sorting. This could be displayed in the report header. e.g. SortDesc: "Sorted by " & Choose([Sort by 1 booking 2 invoice 3 paid], "start date of booking", "invoice date", "date invoice paid")

file: acc_sortbyoptions.htm Page last modified Mar14 2014