Another gotcha in Access is when you use a function of any kind as a query field or as a bound field on a form or report.

This can also be done in Conditional Formatting fields in forms and reports.

Let’s say you bind a continuous form field to the builtin function “=Now()”

You might expect that Now() would run for each record and you would get different results if the query takes more than 1 second to run, but this is not true!

When using a bound function in a query, form, or report across multiple records:

1. If there are no arguments specified for the function, it will calculate the value once when determining the first record values, and then all the remaining rows will display that first value that is returned.
2. If there is an argument specified, but it is a constant or evaluated to a constant, it will have the same behavior and only run that function once.
3. If there is an argument specified for the function that is a field from the recordset, it will re-run the function for every record.

Let’s show an example:

'Placed in a normal module in the program

Public Function GetRowCount(Optional ResetCount As Boolean = False, Optional FieldVar As Variant) As Long
  Static RowCount As Long
  If ResetCount Then RowCount = 0
  RowCount = RowCount + 1
  GetRowCount = RowCount
End Function

'Results in Immediate Window:
'?GetRowCount(True)
' 1 
'?GetRowCount
' 2 
'?GetRowCount
' 3 

This function will print 1 then 2 then 3 and so on with every call, unless you set the first optional variable to True.

Now given this query, you might think it would call the function multiple times, but it does not. It will call the GetRowCount function once and in this case, because it was at 3 with my debug print calls, it would show 4 for all the customer records the query returns.

SELECT Customers.ID, Customers.CustomerName, GetRowCount() AS UserFunction
FROM Customers;

Query1

ID	CustomerName	UserFunction
1	Bill	4
2	Bob	4
3	Shane	4

SELECT Customers.ID, Customers.CustomerName, GetRowCount(False) AS UserFunction
FROM Customers;

And then this query does the same thing because False is a constant.

Query1

ID	CustomerName	UserFunction
1	Bill	5
2	Bob	5
3	Shane	5

But now if I also include the field name as an argument to the function (even though the function doesn’t actually use it), the query will evaluate the function for each row:

SELECT Customers.ID, Customers.CustomerName, GetRowCount(False,[CustomerName]) AS UserFunction
FROM Customers;

Query1

ID	CustomerName	UserFunction
1	Bill	6
2	Bob	7
3	Shane	8