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

ID: Q98788


The information in this article applies to:
  • 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 


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

  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 


Note that the function IncrementRecNum() is run only once.

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 


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 : kbusage
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbinfo


Last Reviewed: October 13, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.