Expression Result
SELECT [FirstName], [LastName] FROM [Employees] WHERE [LastName] = "Davolio"; Displays the values in the FirstName and LastName fields for employees whose last name is Davolio.
SELECT [ProductID], [ProductName] FROM [Products] WHERE [CategoryID] = Forms![New Products]![CategoryID]; Displays the values in the ProductID and ProductName fields in the Products table for records in which the CategoryID value matches the CategoryID value specified in an open New Products form.
SELECT Avg([ExtendedPrice]) AS [Average Extended Price] FROM [Order Details Extended] WHERE [ExtendedPrice] > 1000; Displays in a field named Average Extended Price the average extended price of orders for which the value in the ExtendedPrice field is more than 1,000.
SELECT [CategoryID],Count([ProductID]) AS [CountOfProductID]
FROM [Products] GROUP BY [CategoryID] HAVING Count([ProductID]) > 10;		 Displays in a field named CountOfProductID the total number of products for categories with more than 10 products
Calculate a value using a subquery (SQL statement)  

You can use these expressions in a calculated field in a query.

Expression Description
Category: (SELECT [CategoryName] FROM [Categories] WHERE [Products].[CategoryID] = [Categories].[CategoryID]) Displays in the Category field the CategoryName, if the CategoryID from the Categories table is the same as the CategoryID from the Products table.
AveragePrice: (SELECT AVG([UnitPrice]) FROM [Products] WHERE [Products].[Salesman] = [Categories].[Salesman]) Displays in the AveragePrice field the average of the values in the UnitPrice field for records, where the Salesman from the Categories table is the same as the Salesman in the Products table.
See Also
 See Also
  Calculate the average of the values in a field  

Use the Avg and DAvg functions to count the number of records in a domain, such as a query. Use the Avg function when writing SQL statements, and the DAvg function when writing Visual Basic for Applications (VBA) code, macros, and expressions. Note that you can't use these functions in a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). Additionally, you can't use the DAvg function in an Access project (.adp).

Expression Description
Avg([Freight]) Displays the average of the values of the Freight field.
Avg([Total]+[Tax]) Displays the average of the sum of the values in the Total and Tax fields
DAvg("[Freight]", "Orders", "[ShipCountry] = '" & strCountry & "'AND [ShippedDate] >= #" & dteShipDate Returns the average freight cost for orders shipped to a specified country/region (strCountry) on or after a given date (dteShipDate). The domain is the Orders table.
Examples of conditional expressions  

The following table lists examples of expressions that you can use in calculated controls (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.) on forms, reports, and data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.).

Expression Description
=IIf([Confirmed] = "Yes", "Order Confirmed", "Order Not Confirmed") Displays the message "Order Confirmed" if the value of the Confirmed field is Yes; otherwise, it displays the message "Order Not Confirmed."
=IIf(IsNull([Country])," ", [Country]) Displays an empty string if the value of the Country field is Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.); otherwise, it displays the value of the Country field.
=IIf(IsNull([Region]),[City]&" "& [PostalCode], [City]&" "&[Region]&" " &[PostalCode]) Displays the values of the City and PostalCode fields if Region is Null; otherwise, it displays the values of the City, Region, and PostalCode fields.
=IIf(IsNull([RequiredDate] - [ShippedDate]), "Check for a missing date", [RequiredDate] - [ShippedDate]) Displays the message "Check for a missing date" if the result of subtracting the value of the ShippedDate field from the RequiredDate field is Null; otherwise, it displays the difference between the values of the RequiredDate and ShippedDate fields.
Calculate the sum of the values in a field  

Use the Sum and DSum functions to calculate the sum the values in a field in a domain, such as a query. Use the Sum function when writing SQL statements, and the DSum function when writing Visual Basic for Applications (VBA) code, macros, and expressions. Note that you can't use these functions in a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). Additionally, you can't use the DSum function in an Access project (.adp).

Expression Description
Sum([Freight]) Displays the sum of the values of the Freight field.
Sum([UnitPrice]*[Quantity]) Displays the sum of the product of the UnitPrice and Quantity fields.
[Sales] / Sum([Sales]) * 100 Displays the percentage of sales, determined by dividing the value of the current record's Sales figure by the sum of all the values in the Sales field.

Note  If the control's Format property is set to Percent, don't include the "*100" portion.
DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'") Displays the sum of the values in the Freight field for orders shipped to the United Kingdom. The domain is the Orders table.
See Also
 See Also
Count the number of records in an underlying query (MDB)  

Use the Count and DCount functions to count the number of records in a domain, such as a query. Use the Count function when writing SQL statements, and the DCount function when writing Visual Basic for Applications (VBA) code, macros, and expressions. Note that you can't use these functions in a data access page (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.). Additionally, you can't use the DCount function in an Access project (.adp).

Expression Description
Count([OrderID]) Uses the Count function to display the number of records in the current query.
Count(*) Uses the Count function to count the number of records in the query, including records with Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) (blank) fields.
DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" & strCountry & _ "' AND [ShippedDate] > #" & dteShipDate & "#") Returns the number of orders in the Orders table that were shipped to a specified country/region (strCountry) after a specified ship date (dteShipDate). The domain is the Orders table.
See Also
 See Also