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

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

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 "Building Applications with Microsoft Access" 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 "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

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

1. Create a new global module called RecordNumbers in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0)

2. 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

1. Add the following function to the global module you created in the previous procedure:

         Function ShouldIncrement ()
            RecordNum = RecordNum + 1
            ShouldIncrement = RecordNum
         End Function
   
   

2. 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()
   

3. Run the query. Note that you receive the following result:

         Last Name    RecordNumber
         -------------------------
         Davolio      1
         Fuller       1
         Leverling    1
         Peacock      1
         Buchanan     1
         Suyama       1
         King         1
         Callahan     1
         Dodsworth    1
   
   

Example B: An Expression that Calculates Once Per Record

1. 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
   
   

2. 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
   

3. 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.
   

4. Run the query. Note that you receive the following result:

         Last Name    RecordNumber
         -------------------------
         Davolio      1
         Fuller       2
         Leverling    3
         Peacock      4
         Buchanan     5
         Suyama       6
         King         7
         Callahan     8
         Dodsworth    9
   
   

Example C: An Expression that Calculates More Than Once Per Record

1. 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.

2. 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
   

3. Run the query. Note that you receive the following result:

         Last Name    RecordNumber
         -------------------------
         Davolio      1
         Fuller       2
         Leverling    3
         Peacock      4
         Buchanan     5
         Suyama       6
         King         7
         Callahan     8
         Dodsworth    9
   
   

4. Repeat Step 2 in this example (example C).

5. Add the following criteria under RecordNumber:

         >=0
   

6. Run the query. Note that you now receive the following result:

         Last Name    RecordNumber
         -------------------------
         Davolio      10
         Fuller       11
         Leverling    12
         Peacock      13
         Buchanan     14
         Suyama       15
         King         16
         Callahan     17
         Dodsworth    18