Using Parameters

After you’ve created the necessary parameters, you need to know how to use them within your report. After doing all this work, you don’t want it to go to waste! There are three primary ways of using parameters: filtering records, displaying them directly on the report, and using them in conditional formatting. We’ll discuss the first two methods here, but the discussion of conditional formatting will wait till Chapter 5.

At the beginning of the chapter, we learned how to filter records by setting criteria for a database field to meet in order for a record to be displayed. The discussion was confined to the examples where you hardcode within the selection formula the text/number/date that the field is compared to. The problem with this approach is that the report uses the same filter every time it is run. There isn’t any room for flexibility based on the user’s needs. This is where parameters come in. By using parameters within a record selection formula, the filter changes each time a user runs the report because the user can enter a new value for each parameter and this is immediately reflected in the selection formula.

Selecting Records with Parameters

Using a parameter within a record selection formula involves the same process that was discussed in the first half of the chapter. You follow the steps to set which field to filter on and set the filtering condition (equal to a value, within a range, etc.). The difference being how you set the value to filter with. You now choose a parameter field. Parameters are listed with a ? in front of it. The next figure shows using a parameter to filter the Country field.

Figure 4-13. Setting a filter that uses a parameter.

Filtering on range and multi-value parameters has a few idiosyncrasies that you need to be aware of. As you know, these types of parameters are more complex due to the fact that they store more than just a simple value.

Range parameters consist of a start value and end value. The only conditions you can use with range parameters are Is Equal To and Is Not Equal To. This is the equivalent of testing whether a value falls within the range (Is Equal To) or if it falls outside the range (Is Not Equal To). Multi-value parameters consist of multiple discrete values or multiple range values. Just like range parameters, the only conditions you can use are the Is Equal To and Is Not Equal To conditions. Using the Is Equal To comparison looks at each of the individual values within the parameter and determines if it is equal to any one of those values. It only needs to match one value for the record to match the condition. Consequently, the Is Not Equal To condition is a match if the value isn’t any of the parameter values.