Access VBA Function Examples for Forms, Queries, and Tables

Access VBA functions are reusable procedures that return a value, such as a formatted name, a calculated amount, or a date check. Put a function you want to reuse in a standard module, declare it with Public Function, and call it from a form, report, or query; the examples below show how.

What an Access VBA Function Does

A VBA function takes zero or more inputs, performs work, and returns a result. In VBA, you return a value by assigning it to the function’s name. For example, assigning a number to DaysUntilDue inside that function makes the number available to the caller. A function differs from a Sub, which performs an action but does not return a value in the same way.

Functions are useful when the same rule appears in several places. Instead of maintaining separate versions of a name-formatting rule in multiple forms and queries, you can write it once and call it wherever needed. They can also make a complicated expression easier to read. They are not automatically faster, however: a function that performs a database lookup for every row in a large query can slow that query down.

Access VBA is for the Windows desktop version of Microsoft Access. There is no native Access for Mac application, so the Windows steps and keyboard shortcut below do not have a Mac Access equivalent.

Where to Put the Function

To create a reusable function, open the database in Access and choose Create > Module. The Visual Basic Editor opens with a standard module. Add the function there, then save the module with a descriptive name such as modUtilities. A standard module is generally the right home for a public function called by a query or by multiple forms and reports.

On Windows, Alt+F11 opens the Visual Basic Editor from Access. A function that must be available to other Access objects should normally be declared Public and placed in a standard module, not inside a form’s or report’s class module. A Private function, or one placed in an object module, has a narrower scope and may not be available to a query expression.

When you add or change code, save the module and compile the project in the Visual Basic Editor by choosing Debug > Compile if that command is available. Compilation helps catch syntax and declaration errors before you rely on the function. Give each module and function a clear name, and avoid naming a function after an existing field, control, or built-in function.

Example: Format a Name

This function joins first and last names while handling Null values. In Access, a field with no value is often Null, which is different from an empty string. Using Nz converts Null to an empty string here, and Trim$ removes extra spaces at the ends.

Public Function FormatContactName(ByVal FirstName As Variant, _
                                  ByVal LastName As Variant) As String
    Dim firstPart As String
    Dim lastPart As String

    ' Convert Null values to empty strings and trim extra spaces.
    firstPart = Trim$(Nz(FirstName, vbNullString))
    lastPart = Trim$(Nz(LastName, vbNullString))

    ' Return whichever parts are present, without an extra space.
    If Len(firstPart) = 0 Then
        FormatContactName = lastPart
    ElseIf Len(lastPart) = 0 Then
        FormatContactName = firstPart
    Else
        FormatContactName = firstPart & " " & lastPart
    End If
End Function

Because the arguments are declared as Variant, the function can receive Null from a field. Its result is a string, so it returns an empty string if both inputs are Null or blank. This example only trims the outside of each part; it does not change capitalization or handle middle names. Add those rules only if they match how your database stores and displays names.

Example: Calculate a Line Amount

A small calculation function can standardize arithmetic used in several forms or reports. This example multiplies a unit price by a whole-number quantity and returns a Currency value, a VBA data type suited to fixed-point monetary amounts. It assumes that the caller supplies valid, nonnegative values; validation of user input may belong in the form as well.

Public Function LineAmount(ByVal UnitPrice As Currency, _
                           ByVal Quantity As Long) As Currency
    ' Return the extended amount for one line.
    LineAmount = UnitPrice * Quantity
End Function

For a form text box, set its Control Source to an expression such as =LineAmount([UnitPrice],[Quantity]), assuming those controls or fields contain the inputs. In a report, the same expression can display a consistent line total. A function does not store its result in a table by itself: it calculates a value when Access evaluates the expression. Store calculated values only when there is a specific data-design reason, since saved totals can become inconsistent if their source values later change.

Calling a Function from a Form

Functions can also provide a value to event code. For instance, this button-click procedure calls a date function, then displays a message. The function shown first returns Null for a missing or invalid date and otherwise returns the number of calendar days from today to the due date. A negative result means the date is in the past.

Public Function DaysUntilDue(ByVal DueDate As Variant) As Variant
    ' Return Null when the input is missing or not a date.
    If IsNull(DueDate) Then
        DaysUntilDue = Null
    ElseIf Not IsDate(DueDate) Then
        DaysUntilDue = Null
    Else
        DaysUntilDue = DateDiff("d", Date, CDate(DueDate))
    End If
End Function

Assume a form has a date control named DueDate and a button named cmdCheckDue. In the button’s On Click event, choose [Event Procedure], then use code like this in the form module:

Private Sub cmdCheckDue_Click()
    Dim daysRemaining As Variant

    ' Pass the current control value to the reusable function.
    daysRemaining = DaysUntilDue(Me!DueDate)

    If IsNull(daysRemaining) Then
        MsgBox "Enter a valid due date."
    Else
        MsgBox CStr(daysRemaining) & " days until the due date."
    End If
End Sub

The function belongs in a standard module; the button’s event procedure belongs in the form module because it responds to that form’s control. This distinction is useful: place shared calculation or formatting rules in standard modules, and keep code tied to a particular form’s controls in that form’s class module.

Using Functions in Access Queries

A public function in a standard module can be called in a query expression. For example, a name field could be defined as DisplayName: FormatContactName([FirstName],[LastName]). The query passes the values from each row to the function and displays the returned string. The function must be available to the database, and the database must be able to run its VBA code.

Here is a function that counts open orders for one numeric customer ID. It uses Access’s DCount domain aggregate, so the table and field names in the criteria must match the database. The example assumes CustomerID is a numeric field and Status contains the text value Open.

Public Function OpenOrderCount(ByVal CustomerID As Long) As Long
    Dim criteria As String

    ' Build criteria for the numeric customer ID and text status.
    criteria = "[CustomerID]=" & CustomerID & " AND [Status]='Open'"

    ' DCount returns zero when no matching records are found.
    OpenOrderCount = DCount("*", "Orders", criteria)
End Function

In a query, an expression such as OpenOrders: OpenOrderCount([CustomerID]) displays the count for each customer row. This is clear for a small result set, but domain functions can be evaluated repeatedly—potentially once per row. On large datasets, consider whether a grouped totals query joined to the customer query would calculate the counts more efficiently. Also avoid passing arbitrary text into SQL criteria: this example uses a numeric argument, which keeps the criteria construction simple.

Choosing Between a Function, a Sub, and an Expression

Use a function when a caller needs a returned value. Use a Sub for an action such as opening a form, updating a control, or responding to a button click. For simple arithmetic or text concatenation used in only one place, an Access expression may be easier to maintain than VBA. A shared business rule, Null handling, or logic too complex for a readable expression can justify a VBA function.

Choose a function when several objects need the same calculation or transformation.
Choose a form event procedure or Sub when the main purpose is an action rather than returning a value.
Choose a query expression for straightforward logic that is only needed in that query.
Choose a query join or aggregate instead of repeated domain lookups when working with larger recordsets.

Keep functions focused. A function that formats a name should not also open a form or modify unrelated records. Smaller procedures are easier to test and reuse, and callers can combine their results with other expressions.

Common Errors and Safe Testing

One frequent issue is a query reporting that it cannot find a function. Check that the function is spelled correctly, is declared Public, and is in a standard module. Also check for a compile error elsewhere in the VBA project; unrelated broken code can prevent Access from compiling or running the project. If the function works in a form but not a query, its location and visibility are especially important to inspect.

Other problems often come from data types and Nulls. A function expecting a Long may fail if it receives text or Null; a date conversion may fail if a field contains invalid text. Decide what the function should do with missing or invalid inputs and handle those cases deliberately. Use parameter types that reflect the data, and avoid assuming that every database field is populated just because a few test records are.

Test with ordinary values and edge cases: Null, empty strings, zero, negative values where relevant, and dates at the boundaries your process uses. A query that calls a function should be tested with a small set of records before being used for a large report or update. If code changes records, test on a copy first and verify the result rather than assuming the code affected only the intended rows.

Before running a macro or VBA routine that may alter data, save a separate copy of the database or a backup. Changes made by VBA cannot be undone with Ctrl+Z. Access may also block code in an untrusted database; enable content only for a file you trust, or use an appropriate trusted location set by your organization. Do not lower security settings globally just to make an unknown database run.

Frequently Asked Questions

How do I call a VBA function in an Access query?
Place a Public Function in a standard module, save and compile the code, then use it in a query expression, for example DisplayName: FormatContactName([FirstName],[LastName]). Confirm the database is permitted to run its VBA code.

Can an Access query call a function in a form module?
For a reusable query function, use a public function in a standard module. Form and report modules are class modules tied to particular objects, so their procedures are not the dependable place for a function intended for query expressions.

What is the difference between a VBA function and a Sub in Access?
A function returns a value by assigning it to its own name, so an expression or another procedure can use that result. A Sub performs actions but is not used as a value-returning expression.

Why does Access say it cannot find my function?
Check its spelling, confirm it is declared Public in a standard module, and compile the VBA project. Also look for a name conflict or another compile error, and make sure the database is allowed to run code.

Can I undo changes made by an Access VBA function?
Do not rely on Ctrl+Z for changes made by VBA; they cannot be undone that way. Back up the database before running code that changes records, and test it on a copy first.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.