Microsoft Access Functions Reference

Microsoft Access uses a distinct subset of VBA (Visual Basic for Applications) and Jet/ACE SQL functions for expressions, queries, forms, and reports.

Below is an operational reference guide categorized by functional area, including Access-specific behaviors and SQL equivalents.

1. String Functions

Function	Description & Access Syntax	Notes & Key Behavior
`Len()`	Returns the number of characters in a string. `Len([FirstName])`	Returns `Null` if the string argument is `Null`.
`Mid()`	Extracts a substring starting at a specified character position. `Mid([ProductCode], 3, 4)`	Uses 1-based indexing. Omitting length extracts all remaining characters.
`Left()` / `Right()`	Returns a specified number of characters from the left or right side of a string. `Left([ZipCode], 5)`	Returns `Null` if passed a `Null` value.
`InStr()`	Returns the 1-based starting position of the first occurrence of a substring. `InStr([Email], "@")`	Returns `0` if the substring is not found.
`Replace()`	Replaces all occurrences of a specified substring with another. `Replace([Phone], "-", "")`	Available in Access 2000 and newer.
`Trim()` / `LTrim()` / `RTrim()`	Removes leading and/or trailing spaces from a string. `Trim([Address])`	`LTrim` handles leading spaces; `RTrim` handles trailing spaces.
`UCase()` / `LCase()`	Converts text to uppercase or lowercase. `UCase([StateCode])`	Access SQL equivalent to `UPPER()` / `LOWER()`.
`StrConv()`	Converts a string according to a specified type, such as Proper Case. `StrConv([City], 3)`	Passing `3` as the second argument converts text to Title/Proper Case.

2. Date & Time Functions

Function	Description & Access Syntax	Notes & Key Behavior
`Date()`	Returns the current system date. `Date()`	Access equivalent to `CURRENT_DATE`.
`Now()`	Returns the current system date and time. `Now()`	Access equivalent to `CURRENT_TIMESTAMP` or `GETDATE()`.
`DateAdd()`	Adds or subtracts a specified time interval from a date. `DateAdd("m", 3, [OrderDate])`	Interval codes use quotes: `"yyyy"` (year), `"m"` (month), `"d"` (day).
`DateDiff()`	Calculates the time difference between two dates. `DateDiff("d", [OrderDate], [ShipDate])`	Format: `DateDiff(interval, date1, date2)`.
`DatePart()`	Extracts a specific part, such as year, month, or day, from a date as an integer. `DatePart("yyyy", [OrderDate])`	Alternatives include `Year()`, `Month()`, and `Day()`.
`Year()` / `Month()` / `Day()`	Returns individual date components as integers. `Year([HireDate])`	Useful for filtering or grouping by date parts in queries.
`DateSerial()`	Returns a valid `Date` value for a specified year, month, and day. `DateSerial(2026, 9, 27)`	Useful for constructing dynamic start/end date filters.
`Format()`	Formats a date, number, or string value according to a specified display pattern. `Format([OrderDate], "yyyy-mm-dd")`	Useful for report formatting and query output.

3. NULL Handling & Conditional Logic

MS Access does not support COALESCE(), IF(), or CASE WHEN directly inside query expressions. Instead, it relies on specific VBA/Access evaluation functions:

Function	Description & Access Syntax	Notes & Key Behavior
`IIf()`	Immediate IF: evaluates an expression and returns one of two values. `IIf([Score] >= 50, "Pass", "Fail")`	Access equivalent of `IF()` or `CASE WHEN`. Syntax: `IIf(expr, true_part, false_part)`.
`Nz()`	Null Zero/Empty: replaces `Null` with a default value. `Nz([Phone], "No Phone")`	Commonly used as an Access equivalent of `COALESCE()` or `ISNULL()`. Default replacement is `0` or `""` if omitted.
`IsNull()`	Evaluates whether an expression contains a `Null` value and returns `True` or `False`. `IsNull([ShippingDate])`	Often used inside `IIf()` statements or other expressions.
`Switch()`	Evaluates a list of condition/value pairs and returns the value of the first true condition. `Switch([Status]=1, "Pending", [Status]=2, "Shipped")`	Useful for multi-branch conditional expressions.
`Choose()`	Returns a value from a choice list based on a 1-based index. `Choose([Priority], "Low", "Medium", "High")`	Returns `Null` if the index is out of bounds or `Null`.

4. Domain Aggregate Functions

Access provides specialized Domain Aggregate functions (DSum, DCount, DLookup, etc.) to aggregate or retrieve data outside the current query's underlying record source or table context.

Syntax

DFunction("FieldName", "TableNameOrQuery", "Criteria")

Function	Description & Example
`DLookup()`	Looks up a specific column value from a table or query. `DLookup("[Email]", "Customers", "[CustomerID] = " & [CustID])`
`DCount()`	Counts records matching a specified domain criterion. `DCount("*", "Orders", "[Status] = 'Pending'")`
`DSum()`	Sums numeric values matching a specified domain criterion. `DSum("[TotalAmount]", "Orders", "[CustomerID] = 101")`
`DAvg()`	Calculates the average value matching a specified domain criterion. `DAvg("[UnitPrice]", "Products", "[CategoryID] = 2")`

5. Type Conversion Functions

Functions used to explicitly convert data types inside Access queries and forms:

Function	Description & Example	Target Data Type
`CStr()`	Converts an expression to a string. `CStr([EmployeeID])`	`String` / Text
`CLng()` / `CInt()`	Converts an expression to a long integer or standard integer. `CLng([Quantity])`	`Long` (32-bit) / `Integer` (16-bit)
`CDbl()` / `CCur()`	Converts an expression to double precision or currency. `CCur([Price])`	`Double` / `Currency`
`CDate()`	Converts a valid date string or number to a `Date` type. `CDate("2026-09-27")`	`Date`
`Val()`	Extracts numbers from a string until a non-numeric character is encountered. `Val("123 Units")` — Output: `123`	`Double`