How-to: Microsoft Access functions

A VBA Function can be created either within a single form or report or in a stand-alone module, which can then be called from anywhere in the database. The key difference between a Sub() and a Function() is that functions return a value.

      [Public | Private ] Function name([arg_List]) 
      End Function

   Public      Extend the scope of this function to all procedures in the project.
   Private     Restrict the scope of this function to procedures within the same module.

   name        The name of the function.
   arg_List    Argument variabless passed to the function, comma separated.
               By default, each local variable=argument (ByRef)
               To have each local variable=value of the argument prefix the 
               argument with 'ByValue'.
   code_block  Program code
   expression  The value to return.


Dim intResult as Integer

intResult = DemoFunc(5,10)
msgbox intResult
Function DemoFunc(x,y)
    DemoFunc = x + y
End Function

Most functions can be used in both VBA and in a query by adding the function (and its parameters) to the 'field' line in the query design view.

For example using the builtin Format() and DateDiff() functions:

Select Query

If one or more table columns are used as function parameters, then the function will return a different value for each row of the result set. So the query above will return different results for every row in the table that has a different Purchase_Date.

When a function is used that has no parameters such as Now() then it will return the same value for every row returned by the query. Some VBA functions will be considered 'unsafe expressions' when used within an SQL query.

You can prefix the expression with a meaningful name or allow Access to assign a name automatically Expr1: , Expr2: ...

“If slaughterhouses had glass walls, everyone would be a vegetarian” ~ Paul McCartney


Format - Format a Number/Date/Time.
Now - Return the current date and time.
Sum (SQL) - Add up the values in a query result set.

Copyright © 1999-2024
Some rights reserved