Home	Excel	Access
| Feedback | Assorted Access Queries |

Offering choices of how to sort data in an Access query

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.

Would you like to learn more?

Microsoft Office Access 2003 Step by Step (with CD)

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

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

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
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
Criteria:
Or:

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")