Calculated fields are a special type of field. The value of the field is not stored within the database record, but is instead calculated or computed based on the values of other fields within that record. The calculation is called a formula. This formula can consist of values from other fields, constants, and the results of functions.
To add a calculated field:
On the New Field page, enter the name of your field.
Select Calculated from the Type dropdown. The Calculated Properties section appears.
Enter the Formula that describes your calculation.
Select the format to display the result.
Click Save.
Formulas consist of a combination of field names, constants, operators, and functions. A field name is the name of the field that you specified when that field was created. For example, if you added a field to your database that was named Number of Units, to use that field in a calculation you would type:
[Number of Units]
The square brackets are required to identify all field names. Instead of typing in the field name, you can click the Fields button to the right of the Formula text box. A window will open and display all the field names that are available for use in formulas. You can click on any of the field names and that name will be entered into the Formula text control at the current cursor location.
Note: It is not necessary to modify calculated fields if you change a field name that is being used in a calculation.
A numeric constant is defined as an optional negative sign, followed by one or more digits, followed by an optional decimal separator that must be a period, followed by optional digits. Currency symbols and thousand separators are not allowed in numeric constants.
Some examples of formulas:
|
Formula |
Description |
|
64 * 2.5 |
Multiply two constant numbers |
|
[Assets] - [Liabilities] |
Difference between two fields |
|
[Product Code] + "-" + [Product SKU] |
Concatenate two fields with text constant |
|
DateAdd("MM", 36, [Start Date]) |
Add 36 months to a date |
In calculated fields resulting in a number, you can use the four arithmetic operators. These operators can be applied to number or currency fields and numeric constants.
|
Operator |
Description |
|
+ |
Addition |
|
- |
Subtract |
|
* |
Multiply |
|
/ |
Divide |
To combine text based fields, use the "+" operator.
For a calculation resulting in a number, operator precedence is used to determine the result. Multiplication and division are executed first and then the expression is evaluated from left to right. This precedence can be overridden with the use of parenthesis. For example, the following calculations do not result in the same result:
[Units] * [Unit Cost]
+ 100
[Units] * ([Unit Cost + 100)
When using parentheses, they must be applied in pairs. Formulas that don't contain an equal number of left and right parentheses can not be saved.
Some field types can not be used in calculations. The following fields may not be used in formulas:
Address
Calculated
Created
Modified
Name
Compound fields like Name and Address cannot be used in calculations, but the sub-fields may. Name.First, for instance, can be used while Name cannot. For Database Relation, you can use the related field in a calculation, but not any other fields from the related database.
Dates can be manipulated using the DateAdd, DateDiff, and DatePart functions.
The DateAdd function increases a start date by the specified amount. The DateAdd function has the following definition:
DateAdd (DatePart, Number, Date)
|
Argument |
Description |
|
DatePart |
String constant identifying the portion of the date to be adjusted |
|
Number |
Numeric expression that is the adjustment value |
|
Date |
Date expression that is the starting value |
DateAdd functions can be nested together to allow for manipulation of several parts of the date. For example, to add 1 year and 6 months to a date, the following formula could be used:
DateAdd("MM", 6, DateAdd("YY", 1, [Start Date]))
In the example above, [Start Date] can be a field in this database or a date constant. Date constants must use one of the following two formats:
MM-DD-YYYY
YYYY-MM-DD
This means that "01-05-2005" and "2005-01-05" refer to the same date, January 5, 2005.
The different parts of the date are described in the following table.
|
DatePart |
Constant |
Acceptable Range |
|
Year |
"YY" |
-200 - 1000 |
|
Quarter |
"QQ" |
-800 - 4000 |
|
Month |
"MM" |
-2400 - 12000 |
|
Dayofyear |
"DY" |
-73000 - 365000 |
|
Day |
"DD" |
-73000 - 365000 |
|
Week |
"WK" |
-10400 - 52000 |
|
WeekDay |
"DW" |
-10400 - 52000 |
The DateDiff function returns the difference between two dates. The DateDiff function has the following definition:
DateDiff ("DD", [Date 1], [Date 2])
|
Argument |
Description |
|
DD |
String constant identifying the portion of the date to be used when calculating the difference. Acceptable date part constants are shown in the table below. |
|
[Date 1] |
Date expression that is the starting value. |
|
[Date 2] |
Date expression that is compared to [Date 1] when calculating the difference. |
The DatePart function returns the specified portion of a date. The DatePart function has the following definition:
DatePart ("DD", [Date 1])
|
Argument |
Description |
|
DD |
String constant identifying the portion of the date to be returned. Acceptable date part constants are shown in the table below. |
|
[Date 1] |
Date expression to be evaluated. |
When using the DateDiff function, you must keep in mind that the function works within the boundaries of each date part. For example, even though March 31 and April 1 are just one day apart, when comparing the "MM" (month) date part, DateDiff will return 1, as in "1 month" because the calculation crosses the month boundary. Therefore, this calculation:
DateDiff("MM","3/31/2006","4/1/2006")
returns the same value as this calculation:
DateDiff("MM","3/1/2006","4/1/2006")
The following table defines each date part, provides its constant and boundaries, and also provides return values for the DatePart function.
|
Constant |
Boundary |
DatePart( ) Returns | |
|
Day |
"DD" |
Boundary is at midnight. |
1 - 31 (day of the month)
|
|
Week |
"WK" |
Boundary is between Saturday and Sunday. |
1 - 53 (week of the year)
|
|
Month |
"MM" |
Boundary is between calendar months, for example, March-April. |
1 - 12 (calendar month of the year)
|
|
Quarter |
"QQ" |
Boundary is between quarters, for example, January - March, April-June, September-October, December-January. |
1 - 4 (Jan-Mar = 1, Apr-June = 2, July-Sept = 3, Oct-Dec = 4)
|
|
Year |
"YY" |
Boundary is between calendar years, for example, 2005-2006. |
Calendar year
|
|
Day of the week |
"DW" |
For the DatePart function, this constant represents the day of the week. For the DateDiff function, this constant is equivalent to "DD". |
1 - 7 (Sunday-Saturday)
|
|
Day of the year |
"DY" |
For the DatePart function, this constant represents the day of the year. For the DateDiff function, this constant is equivalent to "DD". |
1-366
|
Additional points to remember when using the DateDiff and DatePart functions:
If the DateDiff or DatePart function is used, the formula cannot contain any other calculations.
You must select Number for the Display As field when defining the DateDiff and DatePart calculation properties.
[Date 1] and [Date 2] may either be a "Date" or "Date and Time" field in this database or a hard-coded date such as "7/31/2006" (including quotes). The acceptable date range is 1/1/1753 to 12/31/9999.
For the DateDiff function, if [Date 1] is after [Date 2], the result will be negative.
There are four display types that can be used when showing the results of a formula for a calculated field:
Currency
Date
Number
Text
Each of these field types has its own properties. For example, when creating a calculated field of display type 'Currency,' the user can select the currency type, the number of decimals, the thousands separator, and the decimal separator.
When you edit a calculated field, the system may return a slightly modified formula. There may be additional parenthesis, or if a field name used in a calculation has changed the new name will be used in the formula.
Once you have successfully created a calculated field, the field becomes available for use in other parts of the application. Calculated fields can be added to the column list of a view it can be used in sorting or grouping, can be used to create a filter, and for a calculated field that results in currency or numeric value, can be used in totaling and subtotaling.
Calculated fields that have been marked searchable can be searched and used to create advanced search criteria.
A database may not contain more than five calculated fields. A formula may not be longer than 2048 characters and may not contain more than 50 elements. An element is defined as an operator, a field, or a constant.