The formula is used to perform a calculation and return a value using the values of one or more columns of a same record.
For example: given a table with two columns "Product Name" and "Cost", we can add a third formula column that will return the value of Cost column includes VAT.
You can use the value of the columns in the formula by writing the name of the column enclosed in square brackets.
Example: [Name] + [Lastname]
If one of the columns used in the formula is not filled, formula does not return a result. To obtain nevertheless a result it is possible to make use of the function ISNULL() to return a default value chosen according to the needs. In the example below the ISNULL() function returns an empty text if the column is not filled: ISNULL([Name], '') + ISNULL([Lastname], '')
Text values must be written in quotes. If these marks are used in the text must be preceded by a quote.
Example: [Name] + ' McGiver'
Numeric values must use the "." to separate decimals.
Example: [Cost] + 5.25
The date values must be enclosed in "#" character, expressed in UTC and in the following format: month/day/year.
Example: [Birthdate] < #1/31/1982#
Yes/No values must be written true or false.
Example: [IsValid] = true
Null values in a formula can be identified with NULL word.
Example: [Name] = NULL
In a formula you can use the following operators:
< (Less than)
> (Greater than)
<= (Less than or equal to)
>= (Greater than or equal)
<> (Not equals)
IN (On the list)
The "IN" operator checks if a text value, numeric or date is in a list.
The "*" and "%" can be used as wildcards in comparisons using the LIKE operator. If the text already contains these characters, then they must be enclosed in square brackets (eg [*] or [%]). If the brackets are already present in the text, then they must be enclosed in square brackets (eg [ or ]). The wildcards are allowed only at the beginning and end of text and are not allowed in the middle of text.
In the formula you can use the following functions:
Returns the number of characters in a text.
Gets one of two values depending on the result of a logical expression.
Example: IIF([Cost] > 50, true, false)
Returns a determined value if a given expression is null.
Example: ISNULL([Cost], 20)
Remove the following characters in a text: \r \n \t, ' '.
Example: TRIM('test ')
Returns a text of specified length starting from a specific point.
Example: SUBSTRING('sample text', 1, 5)
Returns the day of the month of the given Date.
Returns the month number of the Date.
Returns the year of the Date.
Returns the difference in number of days between two dates.
Returns the difference in minutes between two date-time. To return the hours divide the result by 60.
TIMEDIF([date1],[date2]) / 60
Returns a new Date increased by a specified number of days. You can also subtract a number of days by entering a negative value.
Returns a new Date increased by a specified number of months. You can also subtract a number of months by entering a negative value.
Returns a new Date increased by a specified number of years. You can also subtract a number of years by entering a negative value.