Amal Nagm

Use expressions in form and report controls

Enter an expression in a text box control

1. In the Navigation Pane, right-click the form that you want to change, and then click Design View on the shortcut menu.
2. Right-click the text box that you want to change, and then click Properties on the shortcut menu.
3. To manually create an expression, on the Data tab in the property sheet, click the ControlSource property of the text box, and then type = followed by the rest of your expression. For example, to calculate the subtotal shown above, type =Sum([table_field]), making sure that you substitute the name of your field for table_field.
4. To create an expression by using the Expression Builder, click the Build button in the property box. Your property sheet will look similar to the following:
   
   

Use expressions as query criteria

Enter criteria in the query design grid

1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.
2. Click in the Criteria cell in the column for which you want to enter your criteria.
3. To manually create your expression, type your criteria expression.
4. To create your expression by using the Expression Builder, on the Design tab, in the Query Setup group, click Builder. Note Do not precede the criteria expression with the = operator.
   Tip
   If you want a larger area in which to enter an expression, press SHIFT+F2 to display the Zoom box.
   
   

Use expressions to create calculated fields in a query

Create a calculated field in query Design view

1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.
2. Click the Field cell in the column where you want to create the calculated field.
3. To manually create your expression, type your expression.
4. To create your expression by using the Expression Builder, on the Design tab, in the Query Setup group, click Builder. Note Do not precede the criteria expression with the = operator; instead, begin the expression with a descriptive label followed by a colon. For example, type Extended Price: to provide the label for an expression that creates a calculated field called Extended Price. Then, enter the criteria for your expression after the colon.
   

Use expressions in the Validation Rule property of a table field

Enter a validation rule for a field in a table

1. In the Navigation Pane, right click the table that you want to change, and then click Design View on the shortcut menu.
2. In the Field Name column, click the field that you want to change.
3. On the General tab in the property sheet, click the Validation Rule property box.
4. To manually create your expression, type your expression.
5. To create your expression by using the Expression Builder, click the Build button in the property box. Note Do not precede the expression with the = operator when you create a validation rule.
   

Use expressions in the Validation Rule property of a control

Enter a validation rule for a control

1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View on the shortcut menu.
2. Right-click the control that you want to change, and then click Properties on the shortcut menu. Access displays the property sheet for the control.
   
3. Click the All tab, and then click the Validation Rule property box.
4. Type the expression, or click the Build button in the property box to create an expression by using the Expression Builder. Note Do not precede the expression with the = operator when you create a validation rule.
   
5. To customize the text that appears if a user enters data that does not match the validation rule, type the text that you want in the Validation Text property.

Use expressions to set default values for a table field

Enter a default value for a field in a table

1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
2. In the Field Name column, click the field that you want to change.
3. On the General tab in the property sheet, click the Default Value property box.
4. Type your expression, or click the Build button in the property box to create an expression by using the Expression Builder.

Use expressions to set default values for controls

Enter a default value for a control

1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View on the shortcut menu.
2. Right-click the control that you want to change, and then click Properties on the shortcut menu.
3. Click the All tab on the property sheet, and then click the Default Value property box.
4. Type the expression, or click the Build button in the property box to create an expression by using the Expression Builder.

Use expressions to carry out macro actions

Enter a condition for a macro action

1. In the Navigation Pane, right-click the macro that you want to change, and then click Design View on the shortcut menu.
2. If you do not see the Condition column in the macro design grid, on the Design tab (on the Ribbon), in the Show/Hide group, click Conditions.
3. In the Condition column, click the blank cell next to the macro action that you want to change, and then type your conditional expression.

Use expressions to group and sort data in reports

Add grouping and sorting to a report

1. In the Navigation Pane, right-click the report that you want to change, and then click Design View on the shortcut menu.
2. On the Design tab, in the Grouping & Totals group, click Group & Sort. The Group, Sort, and Total pane appears at the bottom of the tab that contains your report.
   
3. To add a grouping level to the report, click Add a group.
4. To add a sort order to the report, click Add a sort. A new group level or sort order appears in the pane, plus a list of the fields that provide data for the report. This figure illustrates a typical new group level (grouping on Category) and sort order (sorting on Manufacturer), plus a list that contains the available fields for grouping and sorting:
   
   
5. In the list that contains the available fields, click expression to start the Expression Builder.
6. Enter your expression in the expression box (the upper box) of the Expression Builder. Make sure that you begin the expression with the equal (=) operator.

Add an expression to an existing group or sort

1. In the Navigation Pane, right-click the report that you want to change, and then click Design View on the shortcut menu.
2. Click the group level or sort order that you want to change.
3. Click the down arrow next to Group on (for grouping levels) or Sort by (for sort orders). A list that contains the available fields appears.
   
4. At the bottom of the list that contains the fields, click expression to start the Expression Builder.
5. Type your expression in the expression box (the upper box) of the Expression Builder. Make sure that you begin your expression with the equal (=) operator.

Use the Expression Builder to create expressions

Understanding the Expression Builder

Expression box

Operator buttons

The middle section of the Expression Builder displays buttons for inserting common arithmetical and logical operators into your expression. To insert an operator in the expression box, click the appropriate button. To display a longer list of operators that you can use in expressions, click the Operators folder in the lower-left column that contains expression elements, and then click the category that you want in the middle column. The right column then lists all the operators in the selected category. To insert an operator, double-click it.

Expression elements

The lower section contains three columns:

• The left column displays folders that list the tables, queries, forms, and reports in your database, as well as the available built-in functions and user-defined functions, constants, operators, and common expressions.
• The middle column lists specific elements or categories of elements for the folder that is selected in the left column. For example, if you click Built-In Functions in the left column, the middle column lists function categories.
• The right column lists the values, if any, for the elements that you selected in the left and middle columns. For example, if you click Built-In Functions in the left column and then click a function category in the middle column, the right column lists all the built-in functions for the selected category.

To construct your expression, you can type text in the expression box and paste elements from the other areas in the Expression Builder. For example, you can click in the lower-left column to see any of the objects in your database, in addition to the available functions, constants, operators, and common expressions. When you click an item in the left column, the contents of the other columns change accordingly. For example, when you double-click Tables in the left column and then click the name of a table, the middle column lists the fields in that table. When you double-click Functions in the left column and then click Built-In Functions, the middle column lists all the function categories, and the right column lists all the functions for those categories. When you double-click a function to insert it into your expression, the function and the text that indicates the needed arguments for that function appear as placeholder text in the expression box. For example, if you double-click the IIf function, the Expression Builder adds the following to the expression box:

IIf («expr», «truepart», «falsepart»)

You must replace the text «expr», «truepart», and «falsepart»with the actual argument values. You can do this directly in the expression box by clicking each placeholder, and then either typing the argument or selecting the argument by using the three expression element lists.

If you double-click two or more functions in succession, for example, the IIf function and then the Round function, the Expression Builder adds both functions to the expression box, separated by the placeholder «Expr»:

IIf («expr», «truepart», «falsepart») «Expr» Round («number», «precision»)

In addition to replacing the placeholders for the function arguments, You must replace the placeholder «Expr» with an operator before the overall expression will be valid.

When you paste an identifier (the name of a table field or control) into your expression, the Expression Builder inserts only the parts of the identifier that are required in the current context. For example, if you start the Expression Builder from the property sheet of a form called Customers, and you then paste an identifier for the Visible property of the form into your expression, the Expression Builder pastes only the property name Visible. If you use this expression outside the context of the form, you must include the full identifier: Forms![Customers].Visible.

Start the Expression Builder from a table

1. In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
2. In the Field Name column, click the field that you want to change.
3. Under Field Properties, click the General tab.
4. Click the property that will contain the expression, and then click the Build button next to the property.

Start the Expression Builder from a form or report

1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View on the shortcut menu.
2. Right-click the control that you want to change, and then click Properties on the shortcut menu.
3. In the property sheet, locate the property that will contain the expression, and then click the Build button next to the property.

Start the Expression Builder from a query

1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.
2. Click the cell in the design grid that will contain the expression. For example, click the Criteria cell for the column where you want to supply criteria, or click the Field cell for the column where you want to create a calculated field.
3. On the Design tab, in the Query Setup group, click Builder.

Table of operators

An operator is a sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators. Access supports a variety of operators, including arithmetic operators such as +, -, multiply (*), and divide (/), in addition to comparison operators for comparing values, text operators for concatenating text, and logical operators for determining true or false values. This article provides details about using these operators.

Arithmetic operators

You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative or vice versa.

Operator	Purpose	Example
+	Sum two numbers.	[Subtotal]+[SalesTax]
-	Find the difference between two numbers or indicate the negative value of a number.	[Price]-[Discount]
*	Multiply two numbers.	[Quantity]*[Price]
/	Divide the first number by the second number.	[Total]/[ItemCount]
\	Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer.	[Registered]\[Rooms]
Mod	Divide the first number by the second number, and then return only the remainder.	[Registered] Mod [Rooms]
^	Raise a number to the power of an exponent.	Number ^ Exponent

Comparison operators

You use the comparison operators to compare values and return a result that is True, False, or Null.

Operator	Purpose	Example
<	Returns True if the first value is less than the second value.	Value1 < Value2
<=	Returns True if the first value is less than or equal to the second value.	Value1 <= Value2
>	Returns True if the first value is greater than the second value.	Value1 > Value2
>=	Returns True if the first value is greater than or equal to the second value.	Value1 >= Value2
=	Returns True if the first value is equal to the second value.	Value1 = Value2
<>	Returns True if the first value is not equal to the second value.	Value1 <> Value2

Note In all cases, if either the first value or the second value is null, the result is then also null. Because null represents an unknown value, the result of any comparison with a null value is also unknown.

Logical operators

You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators.

Operator	Purpose	Example
And	Returns True when Expr1 and Expr2 are true.	Expr1 And Expr2
Or	Returns True when either Expr1 or Expr2 is true.	Expr1 Or Expr2
Eqv	Returns True when both Expr1 and Expr2 are true, or when both Expr1 and Expr2 are false.	Expr1 Eqv Expr2
Not	Returns True when Expr is not true.	Not Expr
Xor	Returns True when either Expr1 is true or Expr2 is true, but not both.	Expr1 Xor Expr2

Concatenation operators

You use the concatenation operators to combine two text values into one.

Operator	Purpose	Example
&	Combines two strings to form one string.	string1 & string2
+	Combines two strings to form one string and propagates null values (if one value is Null, the entire expression evaluates to Null).	string1 + string2

Special operators

You use the special operators to return a True or False result as described in the following table.

Operator	Purpose	Example
Is Null or Is Not Null	Determines whether a value is Null or Not Null.	Field1 Is Not Null
Like "pattern"	Matches string values by using the wildcard operators ? and *.	Field1 Like "instruct*"
Between val1 And val2	Determines whether a numeric or date value is found within a range.	Field1 Between 1 And 10 - OR - Field1 Between #07-01-07# And #12-31-07#
In(val1,val2...)	Determines whether a value is found within a set of values.	Field1 In ("red","green","blue") - OR - Field1 In (1,5,7,9)