Create a field that performs custom calculations or manipulates field values in a query

You can create a new field that displays the results of a calculation you define with an expression.

1. Open the query in Design view.
   
   
2. Do one of the following:
   • Type an expression in an empty cell in the Field row. If the expression includes a field name, you must place brackets around the name.

   

   • If you need help creating the expression, use the Expression Builder. To display it, right-click in the Field cell where you're adding the calculated field, and then click Build. For information on how to use the Expression Builder, click .

     After you press ENTER or move to another cell, Microsoft Access enters the default field name ExprN, where N is an integer incremented for each new expression field in the query. The name appears before the expression and is followed by a colon. In a datasheet, this name is the column heading.

   

   You can select ExprN and type a more descriptive name, such as NewPrice.

3. If the expression includes one or more aggregate functions (Sum, Avg, Count, Min, Max, StDev, or Var), and if the design grid includes another field or fields that you want to use for grouping, click Totals on the toolbar (unless the Total row is already displayed). Leave Group By in the Total cell for the grouping field, and in the calculated field, change Group By to Expression.

   

4. If you want, do any of the following to complete the query:
   • Enter criteria to affect the calculation. For more information, click .
     
     
   • Sort the results. For more information, click .
     
     
   • Set field properties such as Format (since the field doesn't inherit properties from the underlying table). For more information, click .

Notes

• The Order Details Extended query in the Northwind sample database has an example of a calculated field called ExtendedPrice. The Order Subtotals query has an example of a calculated field called Subtotal that calculates on groups of records. To view these queries, open the Northwind database in your Office folder's Samples subfolder and then open the Order Details Extended or Order Subtotals query in Design view. For information on opening Northwind, click .
  
  
• To enter or view an entire expression without scrolling, press SHIFT+F2 to display the Zoom box.
  
  
• To see examples of expressions, click .