The product of the value of the Price control on the subform named Orders Subform on the Orders form and 1.06 (adds 6 percent to the value of the Price control). (Note that 0 refers to the first column, 1 refers to the second column, and so on.) The value of the third column in ProductID, a multiple-column list box on the subform named Orders Subform on the Orders form. The value of the OrderSubtotal control on the subform named Orders Subform on the Orders form. The value of the OrderID control on the Orders form. The following table lists examples of expressions that you can use in calculated controls on forms. You can use an expression to return the value from another field or control. Sometimes, you need a value that exists somewhere else, such as in a field or control on another form or report. If any records in one of the controls that you use in the expression might have a null value, you can avoid Null propagation by converting the null value to zero by using the Nz function - for example, =Nz()+Nz(). Note When you use an arithmetic operator ( +, -, *, and /) in an expression, and the value of one of the controls in the expression is null, the result of the entire expression will be null - this is known as Null propagation. The quotient of the values of the EmployeeTotal and CountryRegionTotal fields or controls. The product of the values of the Quantity and Price fields or controls. The product of the value of the Price field or control and 1.06 (adds 6 percent to the Price value). The interval between the date values of the RequiredDate and ShippedDate fields or controls. The sum of the values of the Subtotal and Freight fields or controls. In the field, or in a control bound to the field, the expression = - 2 returns a date/time value equal to two days before the current values in the RequiredDate field. For example, suppose you have a Date/Time table field named RequiredDate. You can also use expressions to perform arithmetic operations on dates. You can use expressions to add, subtract, multiply, and divide the values in two or more fields or controls. Typically, you use these properties by placing a text box in the header or footer section of the form or report, and then using an expression, such as the ones shown in the following table.įor more information about using headers and footers in forms and reports, see the article Insert page numbers into a form or report.

The Page and Pages properties are available only during printing or print preview, so they do not appear on the property sheet for the form or report. You use the Page and the Pages properties to display or print page numbers in forms or reports. The + operator supports null propagation the & operator does not. Null propagation means that if any component of an expression is null, the entire expression is also null. Uses the + operator and null propagation to display the values of the City and PostalCode controls if the value in the Region field or control is null otherwise, it displays the values of the City, Region, and PostalCode fields or controls, separated by spaces. Uses the IIf function to display the values of the City and PostalCode controls if the value in the Region control is null otherwise, it displays the values of the City, Region, and PostalCode controls, separated by spaces. Uses the Trim function to display the value of the Address control, removing any leading or trailing spaces. Uses the Right function to display the last 2 characters of the value in a field or control called AssetCode. Uses the Left function to display the first character of the value of a field or control called ProductName. In this example, the & operator is used to combine the FirstName field, a space character (enclosed in quotation marks), and the LastName field. The expressions in the following table use the & (ampersand) and the + (plus) operators to combine text strings, built-in functions to manipulate a text string, or otherwise operate on text to create a calculated control.ĭisplays the values that reside in table fields called FirstName and LastName. Note You can also use expressions in a form or report when you Highlight data with conditional formatting. To create a calculated control, you enter an expression in the ControlSource property of the control, instead of in a table field or query. The tables in this section provide examples of expressions that calculate a value in a control located on a form or report.

Microsoft access sample database