Microsoft KB Archive/210554

= ACC2000: Number of Times a Custom Function Runs in a Query =

Article ID: 210554

Article Last Modified on 7/16/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210554



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
A custom function called from a query runs as follows:
 * One time per query if the expression does not reference a field.
 * One time per record if the expression does reference a field.
 * Two times per record if there are criteria on the expression.



MORE INFORMATION
To optimize a query, Microsoft Access does not rerun a custom function unless the value passed to it changes. If the function accepts a field as a parameter, Access must rerun the custom function for each record because the data may change from record to record. If criteria are placed on the result of the function, Access must rerun the function when applying the criteria.

The examples below (examples A, B, and C) demonstrate each of the three scenarios, using a custom function designed to number the records in a query.

NOTE: These examples are simplified to demonstrate the results that you can expect when you use custom functions in a query. Many variables can affect the number of times Access reruns a custom function. For example, if you first minimize and them maximize Access, the process of repainting the screen also includes re-executing any custom function that is part of the query result.

To use examples A, B, and C, follow these steps:

 Start Microsoft Access and open the sample database Northwind.mdb Create a new global module called RecordNumbers.  Add the following lines to the module's Declarations section if they are not already there: Option Explicit Global RecordNum 

Example A: An Expression That Calculates Only Once per Query
  Add the following function to the global module that you created in the previous procedure: Function ShouldIncrement RecordNum = RecordNum + 1 ShouldIncrement = RecordNum End Function  Create a new query based on the Employees table. Drag the Last Name field to the query grid, and then add a column with the following expression:

RecordNumber: ShouldIncrement

</li>  Run the query. Note that you receive the following result: <pre class="fixed_text">Last Name  RecordNumber - Davolio       1 Fuller        1 Leverling     1 Peacock       1 Buchanan      1 Suyama        1 King          1 Callahan      1 Dodsworth     1 </li></ol>

Note that the function IncrementRecNum is run only once.

Example B: An Expression That Calculates Once per Record
  Add the following function, DoesIncrement, to the module RecordNumbers that you created in the first procedure: Function DoesIncrement (AnyValue) RecordNum = RecordNum + 1 DoesIncrement = RecordNum End Function </li>  Open the module RecordNumbers in Design view. On the View menu, click Immediate Window. Type the following statement to initialize the variable RecordNum, and then press ENTER: RecordNum = 0 </li> Create a new query based on the Employees table. Drag the Last Name field to the query grid, and then add a column with the following expression:

RecordNumber: DoesIncrement([EmployeeID])

</li>  Run the query. Note that you receive the following result: <pre class="fixed_text">Last Name  RecordNumber - Davolio       1 Fuller        2 Leverling     3 Peacock       4 Buchanan      5 Suyama        6 King          7 Callahan      8 Dodsworth     9 </li></ol>

Example C: An Expression That Calculates More Than Once per Record
 Create a new query based on the Employees table. Drag the Last Name field to the query grid, and then add a column with the following expression:

RecordNumber: DoesIncrement([EmployeeID])

NOTE: You need to complete step 1 of example B to have the DoesIncrement function available.

</li>  Open the module RecordNumbers in Design view. On the View menu, click Immediate Window. Type the following statement to initialize the variable RecordNum, and then press ENTER: RecordNum = 0 </li>  Run the query. Note that you receive the following result: <pre class="fixed_text">Last Name   RecordNumber - Davolio        1 Fuller         2 Leverling      3 Peacock        4 Buchanan       5 Suyama         6 King           7 Callahan       8 Dodsworth      9 </li> Repeat step 2 in this example (example C).</li> Add the following criteria under RecordNumber:

>=0

</li>  Run the query. Note that you now receive the following result: <pre class="fixed_text">Last Name   RecordNumber - Davolio       10 Fuller        11 Leverling     12 Peacock       13 Buchanan      14 Suyama        15 King          16 Callahan      17 Dodsworth     18 </li></ol>

In this case, Access runs the expression twice, once to create the recordset and again to check the criteria that you specified.

<div class="references_section">