Formula guide
The Formula
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.
How to use the column values in the formula
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], '')
How to write text values, numbers, date and yes/no into the formula
Text
Text values must be written in quotes. If these marks are used in the text must be preceded by a quote.
Example:
[Name] + ' McGiver'
Numbers
Numeric values must use the "." to separate decimals.
Example:
[Cost] + 5.25
Date
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
Yes/No values must be written true or false.
Example:
[IsValid] = true
Using null values
Null values in a formula can be identified with NULL word.
Example:
[Name] = NULL
Using operators in the formula
In a formula you can use the following operators:
+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
% (Module)
< (Less than)
> (Greater than)
<= (Less than or equal to)
>= (Greater than or equal)
<> (Not equals)
= (Equals)
IN (On the list)
LIKE (Contains)
AND
OR
How to use the "IN" operator
The "IN" operator checks if a text value, numeric or date is in a list.
Examples:
Wildcards in the text
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.
Examples:
Functions
In the formula you can use the following functions:
LEN
Returns the number of characters in a text.
Example:
LEN('test')
IIF
Gets one of two values depending on the result of a logical expression.
Example:
IIF([Cost] > 50, true, false)
ISNULL
Returns a determined value if a given expression is null.
Example:
ISNULL([Cost], 20)
TRIM
Remove the following characters in a text: \r \n \t, ' '.
Example:
TRIM('test ')
SUBSTRING
Returns a text of specified length starting from a specific point.
Example:
SUBSTRING('sample text', 1, 5)
DAY
Returns the day of the month of the given Date.
Example:
DAY([date])
MONTH
Returns the month number of the Date.
Example:
MONTH([data])
YEAR
Returns the year of the Date.
Example:
YEAR([data])
DATEDIF
Returns the difference in number of days between two dates.
Example:
DATEDIF([date1],[date2])
TIMEDIF
Returns the difference in minutes between two date-time. To return the hours divide the result by 60.
Example:
TIMEDIF([date1],[date2])
TIMEDIF([date1],[date2]) / 60
ADDDAYS
Returns a new Date increased by a specified number of days. You can also subtract a number of days by entering a negative value.
Example:
ADDDAYS([date],3)
ADDMONTHS
Returns a new Date increased by a specified number of months. You can also subtract a number of months by entering a negative value.
Example:
ADDMONTHS([date],3)
ADDYEARS
Returns a new Date increased by a specified number of years. You can also subtract a number of years by entering a negative value.
Example:
ADDYEARS([date],3)