Calculated Fields and Expressions in Microsoft Access

One of Access's most powerful features is its expression engine — the ability to calculate values on the fly using built-in functions, field references, and operators. Calculated fields appear in queries, forms, and reports, and they let you derive new information from your stored data without duplicating it in the database.

Where Calculated Fields Live

You can create calculated expressions in three places:

Queries — add a calculated column to a SELECT query. The calculation runs when the query executes and the result is available like any other field.

Forms — add a text box with an expression as its Control Source. The calculation updates as you navigate between records.

Reports — add a text box with an expression. Particularly powerful in group footers for aggregates.

Table calculated fields — Access 2010+ allows calculated fields directly in table design. Avoid these — they create hidden dependencies and make the database harder to maintain. Use query calculated fields instead.

Basic Expression Syntax

Access expressions use square brackets to reference field names:

[Quantity] * [UnitPrice]

String concatenation uses the & operator:

[FirstName] & " " & [LastName]

To name a calculated field in a query, use the fieldname: expression syntax:

FullName: [FirstName] & " " & [LastName]
TotalAmount: [Quantity] * [UnitPrice]

Essential Built-in Functions

Date and Time Functions

Date()                    ' Today's date
Now()                     ' Current date and time
Year([OrderDate])         ' Extract the year
Month([OrderDate])        ' Extract the month (1-12)
Day([OrderDate])          ' Extract the day
DateDiff("d", [StartDate], [EndDate])  ' Days between two dates
DateAdd("m", 3, [OrderDate])           ' Add 3 months to a date
Format([OrderDate], "mmmm yyyy")       ' Format as "September 2026"

String Functions

Left([ProductCode], 3)    ' First 3 characters
Right([ProductCode], 4)   ' Last 4 characters
Mid([ProductCode], 2, 3)  ' 3 characters starting at position 2
Len([Description])        ' Length of a string
UCase([LastName])         ' Convert to uppercase
LCase([Email])            ' Convert to lowercase
Trim([Notes])             ' Remove leading/trailing spaces
Replace([Phone], "-", "") ' Remove dashes from phone numbers

Numeric Functions

Round([Amount], 2)        ' Round to 2 decimal places
Int([Value])              ' Integer part (truncates)
Abs([Variance])           ' Absolute value
Sqr([Area])               ' Square root

Conditional Functions

IIF (Immediate If) — the Access equivalent of Excel's IF function:

IIF([Status] = "Active", "Yes", "No")
IIF([Amount] > 1000, [Amount] * 0.9, [Amount])  ' 10% discount over $1000

Switch — evaluates multiple conditions:

Switch(
    [Score] >= 90, "A",
    [Score] >= 80, "B",
    [Score] >= 70, "C",
    [Score] >= 60, "D",
    True, "F"
)

Choose — selects from a list based on an index:

Choose([Quarter], "Q1", "Q2", "Q3", "Q4")

Null Handling

Null values propagate through calculations — [Quantity] * [UnitPrice] returns Null if either field is Null. Use Nz() to substitute a default value:

Nz([Quantity], 0) * Nz([UnitPrice], 0)
Nz([Notes], "No notes provided")

Calculated Fields in Queries

In the Query Designer, add a calculated field in a blank column of the design grid:

DaysOpen: DateDiff("d", [OpenDate], Date())
FullAddress: [Address] & ", " & [City] & ", " & [State] & " " & [Zip]
Margin: ([SalePrice] - [CostPrice]) / [SalePrice]

In SQL view:

SELECT 
    OrderID,
    CustomerID,
    Quantity * UnitPrice AS LineTotal,
    Quantity * UnitPrice * TaxRate AS TaxAmount,
    Quantity * UnitPrice * (1 + TaxRate) AS TotalWithTax
FROM OrderDetails;

Aggregate Functions in Queries

Use aggregate functions with GROUP BY to summarize data:

SELECT 
    CustomerID,
    Count(OrderID) AS OrderCount,
    Sum(OrderTotal) AS TotalSpent,
    Avg(OrderTotal) AS AvgOrderValue,
    Max(OrderDate) AS LastOrderDate
FROM Orders
GROUP BY CustomerID;

Calculated Controls in Forms

In a form, add a text box and set its Control Source to an expression starting with =:

=[Quantity] * [UnitPrice]
=[FirstName] & " " & [LastName]
=DateDiff("d", [DueDate], Date()) & " days overdue"
=IIF([Balance] > 0, "Outstanding: $" & Format([Balance], "0.00"), "Paid in Full")

Calculated controls are read-only — users cannot type into them. They update automatically as the underlying field values change.

Format Function for Display

The Format() function controls how values are displayed:

Format([Amount], "Currency")          ' $1,234.56
Format([Amount], "#,##0.00")          ' 1,234.56
Format([Percentage], "0.0%")          ' 85.3%
Format([OrderDate], "Long Date")      ' September 16, 2026
Format([OrderDate], "mm/dd/yyyy")     ' 09/16/2026
Format([OrderDate], "yyyy-mm-dd")     ' 2026-09-16

Domain Aggregate Functions

Domain aggregate functions let you look up values from other tables within an expression:

DLookup("[CustomerName]", "Customers", "[CustomerID] = " & [CustomerID])
DCount("*", "Orders", "[CustomerID] = " & [CustomerID])
DSum("[Amount]", "Orders", "[CustomerID] = " & [CustomerID])

These are convenient but slow for large datasets. Use them sparingly — a JOIN in a query is almost always faster.

Conclusion

Access expressions are a deep topic, but the fundamentals — IIF, DateDiff, Nz, Format, and basic arithmetic — cover the vast majority of real-world use cases. Build a library of expressions you use regularly, and you will find that calculated fields let you derive rich, meaningful information from your data without ever storing redundant values in your tables.