Recommended Free Tools
In Access, create a calculated value either as a calculated column in a query or as a Calculated field in a table. Use a query when the result should be part of query output or needs fields from more than one source; use a table field when the formula uses fields from that same table and you want the result represented there. For a value that only needs to appear on a form or report, you can instead put an expression in a control’s ControlSource property.
Choose where the calculation belongs
| Option | Use it when | Key constraint |
|---|---|---|
| Query calculated field | You want the value in query results, or the calculation needs fields available through the query’s data sources. | The expression is a query column, not a field added to the underlying table. |
| Table Calculated field | You want a calculated column in a table and its formula uses fields from that same table. | The value is read-only, and the Calculated data type is available only in .accdb databases. |
| Form or report control | You need to display a calculated value on a form or report. | Enter the expression in the control’s ControlSource property. |
Table calculated fields cannot reference fields from another table or query. If a calculation needs a related-table value, build it in a query that includes the required source fields. Microsoft documents these expression options for Access for Microsoft 365, Access 2024, Access 2021, Access 2019, and Access 2016; the table Calculated data type has the additional .accdb restriction.
Create a calculated field in a query
A query expression creates a calculated output column each time the query runs. In Query Design, give the column a descriptive alias, followed by a colon and the formula. For example, to multiply a quantity by a unit price:
Extended Price: [Quantity] * [Unit Price]
- In the Navigation Pane, right-click the query you want to edit and choose Design View.
- In a blank query-design column, click the Field row.
- Enter an alias, a colon, and the expression, such as
Extended Price: [Quantity] * [Unit Price]. - Run the query to see the calculated value for each row.
The text before the colon becomes the output column name. If you omit it, Access may assign a generic name such as Expr1. To construct an expression with assistance, select Expression Builder from the Design tab’s Query Setup group.
Free tools Windows power users keep installed
One-click scans. No signup required.
Create a Calculated field in a table
Use this option when the formula depends only on fields in the table where you are adding the field. In Datasheet view, Access guides you through choosing the result type and entering the expression.
- Open the table in Datasheet View.
- Move to the rightmost column and click Click to Add.
- Select Calculated Field, then choose the result data type.
- Enter the formula in Expression Builder. For example:
[Quantity] * [Unit Price]. - Click OK, type a name in the new field header, and press Enter.
Do not add a leading equals sign to the table expression shown above. The table Calculated field stores a read-only calculated result; it is not an editable place to enter or override a value. Microsoft’s expression guidance states, “The calculation cannot include fields from other tables or queries and the results of the calculation are read-only.”
Rank #2
Write expressions for common calculations
Multiply or add numeric fields
For a query column that calculates an extended price, enter Extended Price: [Quantity] * [Unit Price]. For a table Calculated field, enter the formula without the query alias: [Quantity] * [Unit Price].
Combine text fields
Use the ampersand operator to join text values. For example, a query expression can combine first and last names with a space between them:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsFull Name: [FirstName] & " " & [LastName]
Handle Null values when adding
Access’s example for adding quarterly sales while treating Null as zero is SixMonthSales: Nz([Qtr1Sales]) + Nz([Qtr2Sales]). Use this pattern only when a missing input should count as zero for the calculation; a Null may represent unknown or unavailable data, which is not always equivalent to zero.
Choose a result type and format
For a table Calculated field, choose a result type appropriate for the expression, then set a matching display format where needed. Microsoft notes that the Calculated type has result-type and format properties and advises matching the format to the result type in most cases. The correct choice depends on the expression and the types of its source fields.
Use an expression on a form or report
If the calculated value is needed for display on a form or report rather than as a query column or table field, set the control’s ControlSource property to an expression. This keeps the calculation in the presentation control, while a query calculation belongs in query output and a table Calculated field belongs in the table structure.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Check the syntax and file format
- Query Design: use an output alias, a colon, and then the expression, such as
Extended Price: [Quantity] * [Unit Price]. - Table Calculated field: enter the formula itself in Expression Builder, such as
[Quantity] * [Unit Price]; do not prefix it with=. - Table source fields: reference fields from that table only. Use a query when the expression needs fields from other tables or queries.
- Database format: the Calculated data type is available only in .accdb databases.
Expression syntax varies by location in Access. A query’s alias-and-colon convention is not a universal rule for every expression property or control. If a table does not offer Calculated Field, check whether the database is in .accdb format and whether your Access version is among those covered by Microsoft’s expression guidance.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Quick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Microsoft references
- Learn to build an expression
- Introduction to expressions
- Examples of expressions
- Introduction to data types and field properties
- Introduction to controls
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
