|| Feedback | Assorted Access Queries ||
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.
|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||ox|
|Field:||MySort: Choose([Sort by 1 booki...||UserGroup_Name||Date_out||Date_Return||Invoice_Date|
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||© MeadInKent.co.uk 2014|