Introduction to expressions
You can use expressions for a wide variety of tasks in Microsoft Office Access 2007, such as performing mathematical calculations, combining or extracting text, or validating data. This article provides background information about expressions — when to use them, how they compare with Microsoft Office Excel formulas, and what their component parts are.
In this article
Overview of expressions
An expression is a combination of some or all of the following: built-in or user-defined functions, identifiers, operators, and constants that evaluates to a single value.
Tip: In Access 2010, the Expression Builder has IntelliSense, so that you can see what arguments your expression requires.
For example, the following expression contains all four of these elements:
= Sum( [Purchase Price])*0.08
In this example, Sum() is a built-in function, [Purchase Price] is an identifier, * is a mathematical operator, and 0.08 is a constant. This expression can be used in a text box in a form footer or report footer to calculate sales tax for a group of items.
Expressions can be much more complex or much simpler than this example. For example, this Boolean expression consists of just an operator and a constant:
>0
This expression returns True when it is compared to a number that is greater than 0, and returns False when it is compared to a number that is less than 0. You can use this expression in the Validation Rule property of a control or table field to ensure that only positive values are entered.
In Access, expressions are used in many places to perform calculations, manipulate characters, or test data. Tables, queries, forms, reports, and macros all have properties that accept an expression. For example, you can use expressions in the Control Source and Default Value properties for a control. You can also use expressions in the Validation Rule property for a table field. In addition, when you write Microsoft Visual Basic for Applications (VBA) code for an event procedure or for a module, you often use expressions that are similar to those that you use in an Access object, such as a table or query.
Note: For more information about the components of an expression, see the section Components of an expression, later in this article.
Why use expressions?
In Access, you can use expressions to do any of the following:
-
Calculate values that do not exist directly in your data. You can calculate values for query fields and controls on forms and reports.
-
Set a default value for a table field or for a control on a form or report. These default values appear whenever you open a table, form, or report.
-
Create a validation rule. Validation rules control what values users can enter in a field or control.
-
Specify criteria for a query.
One of the most common ways to use expressions in Access is to calculate values that don't exist directly in your data. For example, you can create a column in a query that calculates a line item total, and then use that value in a form or report. A column in a query that results from such a calculation is called a calculated field. For example, the following expression in a query calculates the line item totals with an applied discount:
ExtendedPrice : CCur ( [Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100
This expression gives the resulting column, or calculated field, the name ExtendedPrice.
You cannot create a table field that automatically calculates its own value. If necessary, you can use a table field to store the results of a calculation that was done elsewhere (for example, in a query or on a form). However, unless there is a specific reason to do so, it is more efficient to store only the values that you need to perform a calculation, and then calculate the results when you need them.
For example, suppose that you need to calculate the total dollar amount for a line item in an invoice. Instead of storing the line item total in the database, you calculate it as needed from two other items that you store in the database — quantity and price. In a text box control on the invoice report, you use the following expression to calculate the value:
= CCur ( [Quantity]*[Price])
This expression multiplies quantity by price, and then uses the convert to currency (CCur) function to convert the result to the Currency data type. You can also calculate a discount for each order.
You can create a calculated field whenever you need to calculate or perform operations on the data in two or more table fields. For example, many tables store first and last names in separate fields. If you want to combine those first and last names and then display them in a single field, you can create a calculated field in a query. As mentioned earlier, creating that type of calculated field directly in a table might seem logical, but tables do not support that type of operation. They do not provide a field or property in which to add the necessary expression.
A calculated field that combines first and last names would look like this: [FirstName] & " " & [LastName]. In this case, the ampersands (&) combine the value in the FirstName field, a space character (a space enclosed in quotation marks), and the value in the LastName field.
You can also use expressions in Access to provide a default value for a field in a table or for a control. For example, to set the default value for a date field to the current date, you type Date() in the Default Value property box for that field.
In addition, you can use an expression to set a validation rule. For example, you can use a validation rule in a control or table field to require that the date that is entered is greater than or equal to the current date. In that case, you set the value in the Validation Rule property box to >= Date().
Finally, you can use an expression to set criteria for a query. For example, suppose that you want to see product sales for the orders that were shipped within a certain time frame. You can enter criteria to define a date range, and Access returns only the rows that match the criteria. For example, your expression might look like this:
Between #1/1/2007# And #12/31/2007#
When you add criteria to the query and then run the query, it returns only those values that match the specified dates.
Comparison of Access expressions and Excel formulas
Access expressions resemble Excel formulas in that both use similar elements to produce a result. Both Excel formulas and Access expressions contain one or more of the following:
-
Identifiers In Excel, identifiers are the names of individual cells or ranges of cells in a workbook, such as A1, B3:C6, or Sheet2!C32. In Access, identifiers are the names of table fields (such as [Contacts]![First Name]), controls on forms or reports (such as Forms![Task List]![Description]), or the properties of those fields or controls (such as Forms![Task List]![Description].ColumnWidth).
-
Operators In both Access and Excel, operators are used to compare values or to perform simple calculations on your data. Examples include + (plus) or - (minus).
-
Functions and arguments In both Access and Excel, functions and arguments are used to accomplish tasks that you can't do by using operators alone — for example, you can find the average of the values in a field, or convert the result of a calculation to a currency format. Examples of functions include SUM and STDEV. Arguments are values that provide information to functions. Access and Excel both have many functions from which to choose, but the names of similar functions in the programs are sometimes different. For example, the AVERAGE function in Excel corresponds to the AVG function in Access.
-
Constants In both Access and Excel, constants are values that do not change — such as strings of text or numbers that are not calculated by using an expression.
Access expressions use operators and constants that are similar to those used in Excel formulas, but Access expressions use different identifiers and functions. You can use an Excel formula in only one place — a cell in a worksheet. However, you can use an Access expression in many places for a wide variety of tasks, including the following:
-
Creating calculated controls on forms and reports
-
Creating calculated fields in queries
-
Serving as criteria in queries
-
Validating data being entered into a field, or into a control on a form
-
Grouping data in reports
You can use either an Access expression or an Excel formula to calculate numeric or date/time values by using mathematical operators. For example, to calculate a discounted price for a customer, you can use the Excel formula =C2*(1-D2) or the Access expression = [Unit Price]*(1-[Discount]).
You can also use an Access expression or an Excel formula to combine, split, or otherwise manipulate strings by using string operators. For example, to combine a first and last name into one string, you can use the Excel formula =D3 & " " & D4 or the Access expression = [First Name] & " " & [Last Name].
Components of an expression
Expressions can consist of identifiers, operators, functions and arguments, constants, and values. The following sections describe these components in more detail. The examples in these sections also provide brief descriptions of the syntax that expressions use.
Identifiers
An identifier is the name of a field, property, or control. You use an identifier in an expression to refer to the value that is associated with a field, property, or control. For example, consider the expression =[RequiredDate]-[ShippedDate]. This expression subtracts the value of the ShippedDate field or control from the value of the RequiredDate field or control. In this expression, both RequiredDate and ShippedDate serve as identifiers.
Operators
Access supports a variety of operators, including common arithmetic operators, such as +, -, * (multiply), and / (divide). You can also use comparison operators such as < (less than) or > (greater than) for comparing values, text operators such as & and + for concatenating (combining) text, logical operators such as Not and And for determining true or false values, and other operators specific to Access.
Functions and arguments
Functions are built-in procedures that you can use in your expressions. You use functions for a wide variety of operations, such as calculating values, manipulating text and dates, and summarizing data. For example, one commonly used function is Date, which returns the current date. You can use the Date function in a variety of ways, such as in an expression that sets the default value for a field in a table. In this example, whenever someone adds a new record, the value for the field is set to the current date by default.
Some functions require arguments. An argument is a value that provides input to the function. If a function requires more than one argument, you separate the arguments with a comma. For example, consider the Date function in the following example expression:
= Format( Date()," mmmm d, yyyy ")
This example uses two arguments. The first argument is the Date() function, which returns the current date. The second argument ("mmmm d, yyyy"), which is separated from the first argument by a comma, specifies a text string to instruct the Format function how to format the returned date value. Note that the text string must be enclosed in quotation marks. This expression also illustrates that you can often use the value returned by one function as an argument to another function. In this case, Date() acts as an argument.
Find links to more information about functions and expression syntax in the See Also section.
Constants
A constant is an item whose value does not change while Access is running. The True, False, and Null constants are frequently used in expressions. You can also define your own constants in VBA code that you can use in VBA procedures. VBA is the programming language that Access uses.
Note: You cannot use VBA constants in custom functions that you use in your expressions. For example, VBA has constants for the days of the week: vbSunday represents Sunday, vbMonday represents Monday, and so on. Each of these constants has a corresponding numeric value. For example, the numeric value for vbSunday is 1, and vbMonday is 2. You cannot use these constants in a custom function that is called from within an expression. You must instead use the numeric values.
Values
You can use literal values in your expressions, such as the number 1,254 or the string "Enter a number between 1 and 10." You can also use numeric values, which can be a series of digits, including a sign and a decimal point, if needed. In the absence of a sign, Access assumes a positive value. To make a value negative, include the minus sign (-). You can also use scientific notation. To do so, include "E" or "e" and the sign of the exponent (for example, 1.0E-6).
When you use text strings as constants, place them within quotation marks to help make sure that Access interprets them correctly. In some circumstances, Access supplies the quotation marks for you. For example, when you type text in an expression for a validation rule or for query criteria, Access surrounds your text strings with quotation marks automatically.
For example, if you type the text Paris, Access displays "Paris" in the expression. If you want an expression to produce a string that is actually enclosed in quotation marks, you enclose the nested string either in single (') quotation marks or within three sets of double (") quotation marks. For example, the following expressions are equivalent:
Forms ![ Contacts]![City]. DefaultValue = ' "Paris" '
Forms ![ Contacts]![City]. DefaultValue = " " "Paris" " "
To use date/time values, enclose the values in pound signs (#). For example, #3-7-07#, #7-Mar-07#, and #Mar-7-2007# are all valid date/time values. When Access encounters a valid date/time value that is enclosed in # characters, it automatically treats the value as a Date/Time data type.
Examples of expressions
The following table lists some sample Access expressions and how they are typically used:
| Expression | Purpose |
| =[RequiredDate]-[ShippedDate] | Calculates the difference between the date values in two text box controls (called RequiredDate and ShippedDate) on a report. |
| Date() | Sets the default value for a Date/Time table field to the current date. |
| ExtendedPrice: CCur([Order Details].Unit Price*[Quantity]*(1-[Discount])/100)*100 | Creates a calculated field named ExtendedPrice in a query. |
| Between #1/1/2007# And #12/31/2007# | Specifies criteria for a Date/Time field in a query. |
| =[Orders Subform].Form!OrderSubtotal | Returns the value of the OrderSubtotal control on the Orders subform that is on the Orders form. |
| >0 | Sets a validation rule for a numeric field in a table — users must enter values greater than zero. |
Some expressions begin with the equal (=) operator, and others do not. When you calculate a value for a control on a form or report, you use the = operator to start the expression. In other instances, such as when you type an expression in a query or in the DefaultValue or ValidationRule property of a field or control, you don't use the = operator unless you are adding the expression to a Text field in a table. In some cases, such as when you add expressions to queries, Access removes the = operator automatically.
No comments:
Post a Comment