Microsoft KB Archive/98788

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

Article ID: 98788

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q98788



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



SUMMARY
A custom function called from a query will run 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 will not rerun a custom function unless the value passed to it changes. If the function accepts a field as a parameter, Microsoft 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, Microsoft 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 you can expect when you use custom functions in a query. Many variables can affect the number of times Microsoft Access reruns a custom function. For example, if you first minimize and them maximize Microsoft Access, the process of repainting the screen also includes re-executing any custom function that is part of the query result.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the &quot;Building Applications with Microsoft Access&quot; manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; manual in Microsoft Access version 2.0

Complete the following steps to use examples A, B, and C:

 Create a new global module called RecordNumbers in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0)  Add the following lines to the module's Declarations section if they are not already there: Option Explicit Global RecordNum 

NOTE: In Microsoft Access version 2.0, the Employees table contains more Employees. If you are performing the following examples in version 2.0, expect more records to be returned.

Example A: An Expression that Calculates Only Once Per Query
  Add the following function to the global module 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 add a column with the following expression:

RecordNumber: ShouldIncrement

  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 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 Debug Window (or Immediate window in versions 1.x and 2.0). Type the following statement to initialize the variable RecordNum:

RecordNum = 0

</li> Create a new query based on the Employees table. Drag the Last Name field to the query grid and add a column with the following expression:

RecordNumber: DoesIncrement([EmployeeID])

NOTE: In versions 1.x and 2.0, there is a space in Employee ID.</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 add a column with the following expression:

RecordNumber: DoesIncrement([EmployeeID])

NOTE: You will need to complete step 1 of example B in order to have the DoesIncrement function available.</li> Open the module RecordNumbers in Design view. On the View menu, click Debug Window (or Immediate window in versions 1.x and 2.0). Type the following statement to initialize the variable RecordNum:

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, Microsoft Access runs the expression twice, once to create the recordset and again to check the criteria you specified.

Additional query words: top 20 limit counter

Keywords: kbinfo kbprogramming kbusage KB98788

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.