To illustrate the importance of using the proper keyword in a formula, let’s look at a final example that shows you each stage of writing a formula and how the output is affected by the evaluation time keyword.

The example report is a customer report that uses the Xtreme.mdb database. The report has the first column as the row number. The row number is tracked using a variable called RowNumber. The formula is as follows:

Global RowNumber As Number ‘Don't use Local b/c that resets the variable
RowNumber = RowNumber + 1
Formula = RowNumber

Figures 7-12 shows the output of this report.

Figure 7-12. Row Number example output with same row number.

Notice that all the row numbers are 1. This is because the default rule states if a formula only has variables in it, then it will be evaluated before any records are read. Even though this formula is placed in the detail section, it only gets evaluated once and the value variable never increases.

To fix this problem, use the keyword WhileReadingRecords in the formula.

WhileReadingRecords
Global RowNumber As Number ‘Don't use Local b/c that resets the variable
RowNumber = RowNumber + 1
Formula = RowNumber

Figure 7-13 shows that the row number is now accurate because the formula is evaluated every time a record is read.

Figure 7-13. Row Number example output with correct row numbers.

Let’s modify the example so that the report uses a grouping section based on the country name.

Figure 7-14. Row Number example output with grouping.

Figure 7-14 shows that the report now groups by country and uses a group header. Unfortunately, this change has introduced a bug in the report because the row numbers are now out of order (see the Australia group). This is because the row number is being calculated while the records are being read. After being read, the rows get resorted based upon their group. The row numbers get resorted as well. To fix this change the formula so that the row number is being evaluated while the records are being printed and not when being read.

WhilePrintingRecords
Global RowNumber As Number ‘Don't use Local b/c that resets the variable
RowNumber = RowNumber + 1
Formula = RowNumber

Figure 7-15 shows that the row number is now accurate.

Figure 7-15. Row numbers are correct with grouping.

This series of examples shows that putting an Evaluation Time keyword at the beginning of a formula has a drastic effect on the formula’s value.