To illustrate the importance of using the proper keyword in a formula, let’s look at an 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 and it is placed in the Details Section. You would assume that it will be called every time a detail row is printed. The formula is as follows:
Figure 7-2. Row Number example output with same row number.
Notice that all the row numbers are 1. This is because the default rule states that if a formula only has variables in it, then it is evaluated before any records are read. Even though this formula is placed in the detail section, it only gets evaluated once and the value never changes.
To fix this problem, use the keyword WhileReadingRecords in the formula.
Figure 7-13 shows that the row number is now accurate because the formula is evaluated every time a record is read.
Figure 7-3. 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-4. Row Number example output with grouping.
Figure 7-4 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. This is because the row number is being calculated while the records are being read. After being read, the rows get sorted based upon their group and the row numbers get reordered during this process. 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.
Figure 7-5 shows that the row number is now accurate.
Figure 7-5. Row numbers are correct with grouping.
This series of examples illustrates that putting an Evaluation Time keyword at the beginning of a formula has a drastic effect on the formula’s value.
