This article explains how to create conditional (also known as Boolean) expressions in Access. A conditional expression evaluates to either true or false, and it then returns a result that meets the condition that you specify. If you use functions in your conditional expressions, you can also specify an action for values that do and do not meet the condition in the expression. For example, you can create an expression that finds all sales with a profit margin of 10 percent or less, and then specify that those numbers appear in red type, or replace the values with a message.
In this article
Understand conditional expressions
A conditional expression is a type of expression that tests your data to see if it meets a condition, and then takes an action depending on the result. For example, an expression can look for date values later than a given start date, and then display an error message when you try to enter a date earlier than the defined start date.
Conditional expressions take the same form and use the same basic syntax as other expressions, and you can use them in the same ways that you use other expressions:
-
For table fields, you add your expression to the Validation Rule property of the field. Users must then enter values in the field that match the conditions in the expression. For example, if you use an expression such as >=#1/1/2000# in a Date/Time field, users must enter values equal to or later than January 1, 2000.
-
For controls on forms, you can add your expression to the Control Source or Validation Rule property of the control. Typically, you add conditional expressions to the Validation Rule property, and you add expressions that calculate values to the Control Source property. For example, using >=#1/1/2000# in the Validation Rule property of a control prevents users from entering an invalid date. Using an expression such as Date() in the Control Source property displays the current date as the default value.
-
For queries, you can add your conditional expressions to a blank cell in the Field row, or you can use an expression in the Criteria row of the query. When you use an expression in the Field row, the results appear as a column in the query results. When you use an expression as criteria for an existing field, the expression acts as a filter and limits the records that query returns.
For example, you can use this expression in the Field row of a query: =IIf([Order Date]<=#04/01/2018#,[Order Date],"Order entered after April 1"). The expression specifies a date criterion (<=#04/01/2018#). When the query runs, it displays all date values that meet the criterion, and any date values that do not meet the criterion are replaced with the message "Order entered after April 1." The steps in the section Add an expression to a query explain how to use this expression in a query.
In contrast, using this expression in the Criteria field of a query returns only those records with dates that meet the criterion: Between #04/01/2018# AND #05/15/2018#.
For more information about creating and using expressions, see the article Learn to build an expression.
Create a conditional expression
The steps in this section explain how to add a conditional expression to a table, a control on a form or report, and a query. Each set of steps uses a slightly different expression to test the values in a Date/Time field and take action, based on whether the date values meet the specified condition.
Add an expression to a table field
-
In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.
-
In the Data Type column, click the Date/Time field.
-
Under Field Properties, on the General tab, click the Validation Rule property box and type the following expression:
>=#01/01/2000#
Note: You do not need to use the U.S. date format. You can use the date format for your country/region or locale. However, you must surround the date value with pound signs (#), as shown.
-
Click the column next to Validation Text and type this text string:
Date must be greater than January 1, 2000.
Again, you can use your local date format.
-
Save your changes, and switch to Datasheet view. To do so, right-click the document tab for the table and click Datasheet View on the shortcut menu.
-
Type a date value in the Date/Time field earlier than January 1, 2000. Access displays the message specified in the Validation Rule property box, and you cannot leave the field unless you enter a value that your expression evaluates as true.
Add an expression to a control
-
In the Navigation Pane, right-click the form that you want to change and click Design View on the shortcut menu.
-
Right-click a control bound to a Date/Time field and then click Properties on the shortcut menu.
The property sheet for the control appears.
-
On either the Data tab or the All tab, click the field next to Validation Rule and type the following expression:
>=#01/01/2000#
Note: You do not need to use the U.S. date format. You can use the date format for your country/region or locale. However, you must surround the date value with pound signs (#), as shown.
-
Click the column next to Validation Text and type this text string:
Date must be greater than January 1, 2000.
-
Save your changes and switch back to Form view. To do so, right-click the document tab for the form and click Form View on the shortcut menu.
Add an expression to a query
-
In the Navigation Pane, right-click the query that you want to change and click Design View on the shortcut menu.
-
Click a blank cell in the Field row of the design grid, and type the following expression:
=IIf([Field_Name]<=#04/01/2018# , [Field_Name] , "Date later than 1 April, 2018")
As you type the expression, make sure that you replace both instances of Field_Name with the name of your Date/Time field. Also, if your table does not contain dates prior to April 1, 2018, alter the dates in the expression to work with your data.
-
Save your changes, and then click Run to view the results.
The expression works as follows: The first argument (=IIf([Field_Name]<=#04/01/2018#) specifies the condition that the data must meet — dates must be on or earlier than 1 April, 2018. The second argument ([Field_Name]) specifies what users see when the condition is true — the dates in the field. The third argument ("Date later than 1 April, 2018")) specifies the message that users see when the data does not meet the condition.
As you proceed, remember that not all conditional expressions use the IIf function. Also, remember that the IIf function is the part of the expression that requires the arguments, and not the expression itself.
For more information about expressions and the ways you can use them, see the article Learn to build an expression.
Examples of conditional expressions
The expressions in the following table show some ways to calculate true and false values. These expressions use the IIf function (Immediate If) to determine if a condition is true or false, and then return one value if the condition is true and another value if the condition is false.
See the article IIf Function for more information.
|
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/region])," ", [Country/region]) |
Displays an empty string if the value of the Country/region field is Null; otherwise, it displays the value of the Country/region field. |
|
=IIf(IsNull([Region]),[City]&" "& [PostalCode], [City]&" "&[Region]&" " &[PostalCode]) |
Displays the values of the City and PostalCode fields if the value of the Region field 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. |
