Home	Excel	Access
Previous | Feedback |

Creating query parameters with choices

When designing a query, you can add parameters which will produce dialog boxes to prompt the user for values at run time. These responses can either be used as values within query fields or as criteria to influence which records are displayed. With a query opened in Design mode, select the menu option Query | Parameters. Add the parameter prompt messages and define the data type of the response. The parameters then become variables which are available within the query. They adopt the name of the parameter prompt and must be contained within square brackets [ ].

If you wish to give the user a choice of two options, the resulting field or criteria can incorporate these choices using the IIF (immediate IF) function. For example: SortByChoice: IIF([Sort by Surname or ForeName]="Surname", [Surname], [Forename]) This user defined field in a query can be used later in a report to determine the order in which records are sorted.

The IIF function however can not easily cope with more than 2 options and in such a situation you can use another function called CHOOSE. It takes the format =CHOOSE(ItemChoice, Value1, Value2, ... ). For example: CHOOSE(3, "April","May","June","July","August") will return "June" (the 3rd item). The numeric value (the first argument) can be a field from a table or a parameter value set within the query.

In this example a query is required to show the total value of either Budgets, Expenditure or Manpower for either a particular area within the organisation.

From the Query | Parameters menu option, the following prompts were added:

These are adapted to create user defined fields and a criteria statement.

The field specifications include:

• ElementType: Choose([Select: 1 Directorate 2 Department 3 Office], "Directorate", "Department", "Office")
• ElementName: Choose([Select: 1 Directorate 2 Department 3 Office], [DirectName], [DepartName], [OffName])
• ValueType: Choose([Select: 1 Budget 2 Expenditure 3 Manpower], "Budget", "Expenditure", "Manpower")
• MyValue: Choose([Select: 1 Budget 2 Expenditure 3 Manpower], [BudValue], [ExpValue], [WTEvalue])

Would you like to learn more?

Microsoft Office Access 2003 Step by Step (with CD)

The criteria specifications include:

• for [ElementName]: Like([Name of Element ]) & "*"

This enables descriptions of the responses, as well as the field values themselves to be made available to a report, graph or form which makes use of the query. A report text field could say: ="Total " & [ValueType] & " for the " & [ElementName] & " " & [ElementType] which would display as "Total Expenditure for the Finance Department".

file: aquerychoose.htm

© MeadInKent.co.uk