This section is a complete reference to all of the EasyScript functions in BIRT Designer Professional. This reference organizes the functions alphabetically. Each function entry includes a general description of the function, its syntax, the arguments to the function, the result the function returns, and an example that shows typical usage.
ABS( )
Returns the absolute value of a number without regard to its sign. For example, 6 is the absolute value of 6 and ‑6.
Syntax
ABS(number)
Argument
number
The number for which you want to find the absolute value.
Returns
An integer that represents the absolute value of a specified number.
Example
The following example returns the absolute value for each number in the TemperatureCelsius field:
ABS([TemperatureCelsius])
ADD_DAY( )
Adds a specified number of days to a date value.
Syntax
ADD_DAY(date, n)
Arguments
date
The date or date expression that represents the start date.
n
The number of days to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date value that results from adding the specified number of days to the start date.
Example
The following example adds 15 days to each date value in the InvoiceDate field:
ADD_DAY([InvoiceDate], 15)
ADD_HOUR( )
Adds a specified number of hours to a date value.
Syntax
ADD_HOUR(date, n)
Arguments
date
The date or date expression that represents the start date. If a start date does not have a time value, the function assumes the time is midnight, 12:00 AM.
n
The number of hours to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date‑and‑time value that results from adding the specified number of hours to the start date.
Example
The following example adds eight hours to each date value in the ShipDate field:
ADD_HOUR([ShipDate], 8)
ADD_MINUTE( )
Adds a specified number of minutes to a date value.
Syntax
ADD_MINUTE(date, n)
Arguments
date
The date or date expression that represents the start date. If a start date does not have a time value, the function assumes the time is midnight, 12:00 AM.
n
The number of minutes to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date‑and‑time value that results from adding the specified number of minutes to the start date.
Example
The following example subtracts 30 minutes from each date in the StartTime field:
ADD_MINUTE([StartTime], ‑30)
ADD_MONTH( )
Adds a specified number of months to a date value.
Syntax
ADD_MONTH(date, n)
Arguments
date
The date or date expression that represents the start date.
n
The number of months to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date value that results from adding the specified number of months to the start date. This function always returns a valid date. If necessary, the day part of the resulting date is adjusted downward to the last day of the resulting month in the resulting year. For example, if you add one month to 1/31/08, ADD_MONTH( ) returns 2/29/08, not 2/31/08 or 2/28/08, because 2008 is a leap year.
Example
The following example adds two months to each date value in the InitialRelease field:
ADD_MONTH([InitialRelease], 2)
ADD_QUARTER( )
Adds a specified number of quarters to a date value.
Syntax
ADD_QUARTER(date, n)
Arguments
date
The date or date expression that represents the start date.
n
The number of quarters to add to the start date. If you specify a negative number, the result is the number subtracted from the start date.
Returns
The date value that results from adding the specified number of quarters to the start date. A quarter is equal to three months. For example, if you add two quarters to 9/22/08, ADD_QUARTER( ) returns 3/22/09.
Example
The following example adds two quarters to each date value in the ForecastClosing field:
ADD_QUARTER([ForecastClosing], 2)
ADD_SECOND( )
Adds a specified number of seconds to a date value.
Syntax
ADD_SECOND(date, n)
Arguments
date
The date or date expression that represents the start date. If a start date does not have a time value, the function assumes the time is midnight, 12:00 AM.
n
The number of seconds to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date‑and‑time value that results from adding the specified number of seconds to the start date.
Example
The following example adds 30 seconds to each date value in the StartTime field:
ADD_SECOND([StartTime], 30)
ADD_WEEK( )
Adds a specified number of weeks to a date value.
Syntax
ADD_WEEK(date, n)
Arguments
date
The date or date expression that represents the start date.
n
The number of weeks to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date value that results from adding the number of weeks to the start date.
Example
The following example adds two weeks to each date value in the OrderDate field:
ADD_WEEK([OrderDate], 2)
ADD_YEAR( )
Adds a specified number of years to a date value.
Syntax
ADD_YEAR(date, n)
Arguments
date
The date or date expression that represents the start date.
n
The number of years to add to the start date. If you specify a negative number, the result is as if the number is subtracted from the start date.
Returns
The date value that results from adding the number of years to the start date.
Example
The following example adds five years to each date value in the HireDate field:
ADD_YEAR([HireDate], 5)
BETWEEN( )
Tests if a value is between two specified values.
Syntax
BETWEEN(source, target1, target2)
Arguments
source
The value to test. The value can be a string, numeric, or date value.
target1
The first value in the range of values to compare to. String and date values must be enclosed in double quotation marks (" ").
target2
The second value in the range of values to compare to. String and date values must be enclosed in double quotation marks (" ").
Returns
True if source is between target1 and target2, or equal to target1 or target2; returns false otherwise.
Examples
The following example tests each value in the SalesTotal field to see if the value is between 10000 and 20000:
BETWEEN([SalesTotal], 10000, 20000)
The following example tests each value in the CustomerName field to see if the value is between A and M:
BETWEEN([CustomerName], "A", "M")
The following example tests each value in the ReceiptDate field to see if the value is between 10/01/07 and 12/31/07:
BETWEEN([ReceiptDate], "10/01/07", "12/31/07")
The following example uses BETWEEN( ) in conjunction with the IF( ) and ADD_DAY( ) functions to calculate a shipment date. If an orderDate value is in December 2007 (between 12/1/07 and 12/31/07), add five days to the orderDate value. If an orderDate value is in a month other than December, add three days to the orderDate value.
Rounds a number up to the nearest specified multiple.
Syntax
CEILING(number, significance)
Arguments
number
The number to round up.
significance
The multiple to round number to.
Returns
The number that results from the rounding. If the specified number value is an exact multiple of significance, no rounding occurs.
Examples
CEILING( ) is commonly used to round up prices. For example, to avoid dealing with pennies, you can round prices in a Price field up to the nearest nickel with the following expression:
CEILING([Price], 0.05)
If the Price value is 20.52, CEILING( ) returns 20.55.
The following example rounds prices up to the nearest dime:
CEILING([Price], 0.1)
If the Price value is 20.52, CEILING( ) returns 20.60. If the Price value is 20.50, CEILING( ) returns 20.50. No rounding occurs because 20.50 is already a multiple of 0.1.
The following example rounds prices up to the nearest dollar:
CEILING([Price], 1)
If the Price value is 20.30, CEILING( ) returns 21.0.
DAY( )
Returns a number from 1 to 31 that represents the day of the month.
Syntax
DAY(date)
Argument
date
The date or date expression from which you want to extract the day.
Returns
The number of the day of the month for the specified date value.
Example
The following example gets the number of the day for each date value in the ShipDate field:
DAY([ShipDate])
DIFF_DAY( )
Calculates the number of days between two date values.
Syntax
DIFF_DAY(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of days between date1 and date2. If date1 is earlier than date2, the result is a positive number; otherwise the result is a negative number.
Example
The following example calculates the time it takes to pay invoices by computing the number of days between each value in the invoiceDate field and each value in the paymentDate field:
DIFF_DAY([invoiceDate],[paymentDate])
The following example calculates the number of days from an order date to Christmas:
DIFF_DAY([orderDate], "12/25/10")
The following example calculates the number of days from the current date to Christmas. TODAY( ) is a function that returns the current date.
DIFF_DAY(TODAY(), "12/25/10")
DIFF_HOUR( )
Calculates the number of hours between two date values.
Syntax
DIFF_HOUR(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
date2
The second date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
Returns
The number of hours between date1 and date2.
Example
The following example calculates the number of hours between each value in the startTime field and each value in the finishTime field:
DIFF_HOUR([startTime],[finishTime])
The following example calculates the number of hours from the current date to Christmas. NOW( ) is a function that returns the current date and time.
DIFF_HOUR(NOW(), "12/25/10")
DIFF_MINUTE( )
Calculates the number of minutes between two date values.
Syntax
DIFF_MINUTE(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
date2
The second date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
Returns
The number of minutes between date1 and date2.
Example
The following example calculates the number of minutes between each value in the startTime field and each value in the finishTime field:
DIFF_MINUTE([startTime],[finishTime])
The following example calculates the number of minutes from the current date to Christmas. NOW( ) is a function that returns the current date and time.
DIFF_MINUTE(NOW(), "12/25/10")
DIFF_MONTH( )
Calculates the number of months between two date values.
Syntax
DIFF_MONTH(date1,date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of months between date1 and date2. The function calculates the difference by subtracting the month number of date1 from the month number of date2. For example, if date1 is 8/1/08 and date2 is 8/31/08, DIFF_MONTH( ) returns 0. If date1 is 8/25/08 and date2 is 9/5/08, DIFF_MONTH( ) returns 1.
Example
The following example calculates the number of months between each value in the askByDate field and each value in the ShipByDate field:
DIFF_MONTH([askByDate],[shipByDate])
The following example calculates the number of months from each value in the hireDate field to the end of the year:
DIFF_MONTH([hireDate], "12/31/10")
DIFF_QUARTER( )
Calculates the number of quarters between two date values.
Syntax
DIFF_QUARTER(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of quarters between date1 and date2. DIFF_QUARTER( ) calculates the difference by subtracting the quarter number of date1 from the quarter number of date2. For example, if date1 is 1/1/10 and date2 is 3/31/10, DIFF_QUARTER( ) returns 0 because both dates are in quarter 1. If date1 is 3/31/10 and date2 is 4/15/10, DIFF_QUARTER( ) returns 1 because date1 is in quarter 1 and date2 is in quarter 2.
Example
The following example calculates the number of quarters between each value in the PlanClosing field and each value in the ActualClosing field:
DIFF_QUARTER([PlanClosing],[ActualClosing])
The following example calculates the number of quarters from each value in the orderDate field to the end of the year:
DIFF_QUARTER([orderDate], "12/31/10")
DIFF_SECOND( )
Calculates the number of seconds between two date values.
Syntax
DIFF_SECOND(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
date2
The second date or date expression to use in the calculation. If the date does not have a time value, the function assumes the time is midnight, 12:00 AM.
Returns
The number of seconds between date1 and date2.
Example
The following example calculates the number of seconds between each value in the startTime field and each value in the finishTime field:
DIFF_SECOND([startTime],[finishTime])
The following example calculates the number of seconds from the current date to Christmas. NOW( ) is a function that returns the current date and time.
DIFF_SECOND(NOW(), "12/25/10")
DIFF_WEEK( )
Calculates the number of weeks between two date values.
Syntax
DIFF_WEEK(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of weeks between date1 and date2. The function calculates the difference by subtracting the week number of date1 from the week number of date2. For example, if date1 is 1/1/10 (week 1 of the year), and date2 is 1/4/10 (week 2 of the year), DIFF_WEEK( ) returns 1.
Example
The following example calculates the number of weeks between each value in the askByDate field and each value in the shipByDate field:
DIFF_WEEK([askByDate],[shipByDate])
The following example calculates the number of weeks from each value in the orderDate field to the end of the year:
DIFF_WEEK([orderDate], "12/31/10")
DIFF_YEAR( )
Calculates the number of years between two date values.
Syntax
DIFF_YEAR(date1, date2)
Arguments
date1
The first date or date expression to use in the calculation.
date2
The second date or date expression to use in the calculation.
Returns
The number of years between date1 and date2. The function calculates the difference by subtracting the year number of date1 from the year number of date2. For example, if date1 is 1/1/10 and date2 is 12/31/10, DIFF_YEAR( ) returns 0. If date1 is 11/25/09 and date2 is 1/5/10, DIFF_YEAR( ) returns 1.
Example
The following example calculates the number of years between each value in the HireDate field and each value in the TerminationDate field:
DIFF_YEAR([HireDate],[TerminationDate])
The following example calculates the number of years from each value in the HireDate field to the current date. TODAY( ) is a function that returns the current date.
DIFF_YEAR([HireDate], TODAY())
FIND( )
Finds the location of a substring in a string.
Syntax
FIND(target, source)
FIND(target, source, index)
Arguments
target
The substring to search for. The search is case‑sensitive.
source
The string in which to search.
index
The position in str where the search starts.
Returns
The numerical position of the substring in the string. The first character of a string starts at 1. If the substring is not found, FIND( ) returns 0.
Examples
The following example searches for the substring, Ford, in each ProductName value:
FIND("Ford", [ProductName])
If the product name is 1969 Ford Falcon, FIND( ) returns 6.
The following example searches for the first hyphen (‑) in each product code:
FIND("‑", [ProductCode])
If the product code is ModelA‑1234‑567, FIND( ) returns 7.
The following example uses FIND( ) in conjunction with the LEFT( ) function to display the characters that precede the hyphen in a product code. The LEFT( ) function extracts a substring of a specified length, starting from the first character. In this example, the length of the substring to display is equal to the numerical position of the hyphen character.
LEFT([ProductCode], FIND("‑", [ProductCode]))
If the product code is ModelA‑1234, the expression returns the following string:
ModelA
IF( )
Returns one value if a specified condition evaluates to true, or another value if the condition evaluates to false.
Syntax
IF(c, vt, vf)
Arguments
c
The condition to test.
vt
The value to return if the condition evaluates to true.
vf
The value to return if the condition evaluates to false.
Returns
Returns the vt value if c is TRUE or the vf value if c is false.
Example
The following example calculates and displays different discount amounts based on the value in the Total field. If the Total value is greater than 5000, the discount is 15%. Otherwise, the discount is 10%.
IF([Total]>5000, [Total]*15%, [Total]*10%)
The following example uses IF( ) in conjunction with the BETWEEN( ) and ADD_DAY( ) functions to calculate a shipment date. If an orderDate value is in December 2010 (between 12/1/10 and 12/31/10), add five days to the orderDate value. If an orderDate value is in a month other than December, add three days to the orderDate value.
The following example checks each value in the Office field. If the value is Boston, San Francisco, or NYC, display U.S. If the value is something other than Boston, San Francisco, or NYC, display Europe and Asia Pacific.
IF([Office]="Boston" OR [Office]="San Francisco" OR [Office]="NYC", "U.S.", "Europe and Asia Pacific")
IN( )
Tests if a value is equal to a value in a list.
Syntax
IN(source, target1,..., targetN)
Arguments
source
The value to test. The value can be a string, numeric, or date value.
target1, ..., targetN
The value or values to compare to.
Returns
True if the source value is equal to one of the target values; returns false otherwise.
Example
The following example tests if New Haven, Baltimore, or Cooperstown are values in the city field. If any one of the cities is in the field, IN( ) returns true.
IN([city], "New Haven", "Baltimore", "Cooperstown")
The following example tests if 9/15/08 or 9/30/08 are values in the payDate field:
IN([payDate], "9/15/08", "9/30/08")
The following example uses IN( ) in conjunction with the IF( ) function to test if Ships or Trains are values in the ProductLine field. If Ships or Trains is a value in the field, display Discontinued Item; otherwise, display the product line value as it appears in the field.
Tests if a value in a specified field is a null value. A null value means that no value exists.
Syntax
ISNULL(source)
Argument
source
The field in which to check for null values.
Returns
True if a value in the specified field is a null value; returns false otherwise.
Example
The following example uses ISNULL( ) in conjunction with the IF( ) function to test for null values in the BirthDate field. If there is a null value, display No date specified; otherwise display the BirthDate value.
IF(ISNULL([BirthDate]), "No date specified", [BirthDate])
LEFT( )
Extracts a substring from a string, starting from the left‑most, or first, character.
Syntax
LEFT(source)
LEFT(source, n)
Arguments
source
The string from which to extract a substring.
n
The number of characters to extract, starting from the first character.
Returns
A substring of a specific length.
If you omit n, the number of characters to extract, the function returns the first character only.
If n is zero, the function returns an empty string.
If n is greater than the length of the string, the function returns the entire string.
Example
The following example displays the first letter of each name in the CustomerName field:
LEFT([CustomerName])
The following example uses the LEFT( ) and FIND( ) functions to display the characters that precede the hyphen in a product code:
LEFT([ProductCode], FIND("‑", [ProductCode]))
If the product code is ModelA‑1234, the expression returns the following string:
ModelA
LEN( )
Counts the number of characters in a string.
Syntax
LEN(source)
Argument
source
The string expression to evaluate.
Returns
The number of characters in the specified string.
Example
The following example returns the length of each value in the ProductCode field:
LEN([ProductCode])
The following example uses LEN( ) in conjunction with the RIGHT( ) and FIND( ) functions to display the characters that appear after the hyphen in a product code. RIGHT( ) extracts a substring of a specified length, starting from the last character. In this example, the length of the entire string returned by LEN( ) minus the length up to the hyphen is the number of characters to display:
If the product code is ModelA‑Ford, the expression returns Ford.
LIKE( )
Tests if a string matches a pattern.
Syntax
LIKE(source, pattern)
source
The string to evaluate.
pattern
The string pattern to match. You must enclose the pattern in double quotation marks (" "). The match is case‑sensitive. You can use the following special characters in a pattern:
A percent character (%) matches zero or more characters. For example, %ace% matches any string value that contains the substring ace, such as Facebook, and MySpace. It does not match Ace Corporation because this string contains a capital A, and not the lowercase a.
An underscore character (_) matches exactly one character. For example, t_n matches tan, ten, tin, and ton. It does not match teen or tn.
To match a literal percent (%), underscore (_), precede those characters with two backslash (\\) characters. For example, to see if a string contains M_10, specify the following pattern:
"%M\\_10%"
Returns
True if the string matches the pattern; returns false otherwise.
Example
The following example returns true for values in the customerName field that start with D:
LIKE([customerName], "D%")
The following example returns true for productCode values that contain the substring Ford:
LIKE([productCode], "%Ford%")
The following example uses two LIKE( ) expressions to look for the substrings "Ford" or "Chevy" in each ProductName value. If a product name contains either substring, the expression displays U.S. Model; otherwise, it displays Imported Model.
IF(((LIKE([ProductName], "%Ford%") = TRUE) OR (LIKE([ProductName], "%Chevy%") = TRUE)), "U.S. model", "Imported Model")
LOWER( )
Converts all letters in a string to lowercase.
Syntax
LOWER(source)
Argument
source
The string to convert to lowercase.
Returns
The specified string in all lowercase letters.
Example
The following example displays all the string values in the productLine field in lowercase:
LOWER([productLine])
MATCH( )
Tests if a string matches a pattern. The pattern must use JavaScript regular expression syntax.
Syntax
MATCH(source, pattern)
Arguments
source
The string to evaluate.
pattern
The string pattern to match. You must enclose the pattern in quotation marks (" "). In JavaScript regular expression syntax, a pattern is enclosed within a pair of forward slash (/) characters. However, for this argument, the forward slash characters are optional. For example, the following values are equivalent:
"smith"
"/smith/"
You can use any special character supported by JavaScript regular expressions, such as the following:
A question mark (?) matches zero or one occurrence of the character previous to it. For example, "te?n" matches tn, ten, and often. It does not match teen or intern.
An asterisk (*) matches zero or any number of occurrences of the character precious to it. For example, "te*n" matches tn, ten, often, and teen. It does not match intern.
A period (.) matches any character. For example, "te.*" matches ten, often, teen, and intern.
A caret (^) specifies that the pattern to look for is at the beginning of a string. For example, "^ten" matches ten, tennis, and tense. It does not match often or pretend.
An i character specifies a case-insensitive search. For example, "/smith/i" matches Smith, blacksmith, and Smithsonian. In this case, the pair of forward slashes is required.
To match a special character literally, precede the special character with two backslash (\\) characters. For example, to check if a string contains S*10, specify the following pattern:
"/S\\*10/"
Returns
True if the string matches the pattern; returns false otherwise.
Examples
The following example returns true for values in the ProductCode field that start with S18:
MATCH([ProductCode], "/^S18/")
The following example uses MATCH( ) to check if the values in the SKU field contain the letters EM followed by a number that ends with 99. If there is a match, display Discontinued; otherwise, display the SKU value.
Returns the remainder after a number is divided by another.
Syntax
MOD(number, divisor)
Arguments
number
The number to divide.
divisor
The number by which to divide the number value. You must specify a non‑zero number.
Returns
The remainder after the number value is divided by the divisor value. Different applications and programming languages define the modulo operation differently when either the dividend or the divisor are negative. For example, in EasyScript and Excel, MOD(-5, 3) returns 1. However, in JavaScript and most databases, the modulo operation returns ‑2.
Examples
The following examples shows the results that the function returns for specific numbers:
MOD(10, 5) // returns 0
MOD(11, 5) // returns 1
MOD(12, 5) // returns 2
MOD(-10, 5) //returns 0
MOD(-11, 5) //returns 4
MOD(-12, 5) //returns 3
MOD(10, -5) //returns 0
MOD(11, -5) //returns -4
MOD(12, -5) //returns -3
The following example uses MOD( ) to check if numbers in the Grade field are odd or even. When the divisor is 2, MOD( ) returns 0 for even numbers, and 1 for odd numbers.
MOD([Grade], 2)
The following example uses MOD( ) and YEAR( ) to get the last digit of a year. YEAR( ) returns the year number of a date. Dividing a number by 10 returns the last digit of the number.
MOD(YEAR([BirthDate]), 10)
MONTH( )
Returns the month for a specified date value.
Syntax
MONTH(date)
MONTH(date, option)
Arguments
date
The date or date expression whose month to get.
option
A number that represents the month format to return. Use one of the following values:
1 to get the month as a number from 1 to 12.
2 to get the full month name, for example, January. The result is locale‑specific.
3 to get the abbreviated month name, for example, Jan. The result is locale‑specific.
If you omit option, MONTH( ) returns the month as a number.
Returns
The month for a specified date value.
Example
The following example returns the month, 1–12, for each value in the ShipDate field:
MONTH([ShipDate])
The following example returns the full month name for each ShipDate value:
MONTH([ShipDate], 2)
NOT( )
Negates a Boolean expression.
Syntax
NOT(x)
Argument
x
The Boolean value or expression to negate.
Returns
True if the expression evaluates to false, and false if the expression evaluates to true.
Example
The following example uses NOT( ) in conjunction with the IF( ) function. It tests if the value in the State field is not CA. If the value is not CA, it returns the value in the Markup field multiplied by 10%, and by 15% if it is.
IF(NOT([State]="CA"),[Markup]*10%,[Markup]*15%)
The previous IF( ) expression is semantically equivalent to the following expression:
IF([State]="CA",[Markup]*15%,[Markup]*10%)
NOTNULL( )
Tests if a value in a specified field is a non‑null value.
Syntax
NOTNULL(source)
Argument
source
The field in which to check for non‑null values.
Returns
True if a value in the specified field is not a null value; returns false otherwise.
Example
The following example uses NOTNULL( ) in conjunction with the IF( ) function to test for non‑null values in the BirthDate field. If there is a non‑null value, display the BirthDate value; otherwise display No date specified.
IF(NOTNULL([BirthDate]), [BirthDate], "No date specified")
NOW( )
Returns the current date and time.
Syntax
NOW( )
Returns
The current date and time. For example:
Feb 10, 2010 2:55 PM
Example
The following example uses the DIFF_DAY( ) and NOW( ) functions to calculate the number of days from the current date and time to Christmas:
DIFF_DAY(NOW(), "12/25/10")
QUARTER( )
Returns the quarter number for a specified date value.
Syntax
QUARTER(date)
Arguments
date
The date or date expression whose quarter number to get.
Returns
A number from 1 to 4 that represents the quarter for a specified date value. Quarter 1 starts in January.
Examples
The following example displays the quarter number for each value in the CloseDate field:
QUARTER([CloseDate])
The following example displays a string—Q1, Q2, Q3, or Q4—for each value in the CloseDate field:
"Q" & QUARTER([CloseDate])
RIGHT( )
Extracts a substring from a string, starting from the right‑most, or last, character.
Syntax
RIGHT(source)
RIGHT(source, n)
Arguments
source
The string from which to extract a substring.
n
The number of characters to extract, starting from the last character.
Returns
A substring of a specific length.
If you omit n, the number of characters to extract, the function returns the last character only.
If n is zero, the function returns an empty string.
If n is greater than the length of the string, the function returns the entire string.
Example
The following example displays the last four characters of each value in the ProductCode field:
RIGHT([ProductCode], 4)
The following example uses RIGHT( ) in conjunction with the LEN( ) and FIND( ) functions to display the characters that appear after the hyphen in a product code. This example assumes that the number of characters after the hyphen varies. Therefore, the length of the entire string (returned by LEN( )) minus the length up to the hyphen (returned by FIND( )) is the number of characters to display.
If the product code is ModelA‑Ford, the expression returns Ford. If the product code is ModelCZ15‑Toyota, the expression returns Toyota.
ROUND( )
Rounds a number to a specified number of digits.
Syntax
ROUND(number)
ROUND(number, dec)
Arguments
number
The number to round.
dec
The number of digits to round number to. If you omit dec, ROUND( ) assumes 0.
Returns
A number rounded to a specified number of digits.
Example
The following example rounds the numbers in the PriceEstimate field to return an integer. For example, if the PriceEstimate value is 1545.50, ROUND( ) returns 1546. If the PriceEstimate value is 1545.25, ROUND( ) returns 1545.
ROUND([PriceEstimate])
The following example rounds the numbers in the PriceEstimate field to one decimal place. For example, if the PriceEstimate value is 1545.56, ROUND( ) returns 1545.6. If the PriceEstimate value is 1545.23, ROUND( ) returns 1545.2.
ROUND([PriceEstimate], 1)
The following example rounds the numbers in the PriceEstimate field to one digit to the left of the decimal point. For example, if the PriceEstimate value is 1545.56, ROUND( ) returns 1550. If the PriceEstimate value is 1338.50, ROUND( ) returns 1340.
ROUND([PriceEstimate], ‑1)
ROUNDDOWN( )
Rounds a number down to a specified number of digits.
Syntax
ROUNDDOWN(number)
ROUNDDOWN(number, dec)
Arguments
number
The number to round down.
dec
The number of digits to round number down to. If you omit dec, ROUND( ) assumes 0.
Returns
A number rounded down to a specified number of digits.
Example
The following example rounds down the numbers in the PriceEstimate field to return an integer. For example, if the PriceEstimate value is 1545.25, ROUNDDOWN( ) returns 1545. If the PriceEstimate value is 1545.90, ROUNDDOWN( ) returns 1545.
ROUNDDOWN([PriceEstimate])
The following example rounds down the numbers in the PriceEstimate field to one decimal place. For example, if the PriceEstimate value is 1545.56, ROUNDDOWN( ) returns 1545.5. If the PriceEstimate value is 1545.23, ROUNDDOWN( ) returns 1545.2.
ROUNDDOWN([PriceEstimate], 1)
The following example rounds the numbers in the PriceEstimate field down to one digit to the left of the decimal point. For example, if the PriceEstimate value is 1545.56, ROUNDDOWN( ) returns 1540. If the PriceEstimate value is 1338.50, ROUNDDOWN( ) returns 1330.
ROUNDDOWN([PriceEstimate], ‑1)
ROUNDUP( )
Rounds a number up to a specified number of digits.
Syntax
ROUNDUP(number)
ROUNDUP(number, dec)
Arguments
number
The number to round up.
dec
The number of digits to round number up to. If you omit dec, ROUND( ) assumes 0.
Returns
A number rounded up to a specified number of digits.
Example
The following example rounds up the numbers in the PriceEstimate field to return an integer. For example, if the PriceEstimate value is 1545.25, ROUNDUP( ) returns 1546. If the PriceEstimate value is 1545.90, ROUNDUP( ) returns 1546.
ROUNDUP([PriceEstimate])
The following example rounds up the numbers in the PriceEstimate field to one decimal place. For example, if the PriceEstimate value is 1545.56, ROUNDUP( ) returns 1545.6. If the PriceEstimate value is 1545.23, ROUNDUP( ) returns 1545.3.
ROUNDUP([PriceEstimate], 1)
The following example rounds up the numbers in the PriceEstimate field to one digit to the left of the decimal point. For example, if the PriceEstimate value is 1545.56, ROUNDUP( ) returns 1550. If the PriceEstimate value is 1338.50, ROUNDUP( ) returns 1340.
ROUNDUP([PriceEstimate], ‑1)
SEARCH( )
Finds the location of a substring in a string. The substring can contain wildcard characters.
Syntax
SEARCH(pattern, source)
SEARCH(pattern, source, index)
Arguments
pattern
The string pattern to search for. You must enclose the pattern in double quotation marks (" "). You can use the following special characters in a pattern:
An asterisk ( * ) matches zero or more characters, including spaces. For example, t*n matches tn, tin, and teen.
A question mark (?) matches exactly one character. For example, t?n matches tan, ten, tin, and ton. It does not match teen or tn.
source
The string in which to search.
index
The position in source where the search starts.
Returns
The numerical position of the string pattern in the string. The first character of a string starts at 1. If the substring is not found, SEARCH( ) returns 0.
Examples
The following example searches for the string pattern, S*A, in each product code. If the product name is KBS5412A, SEARCH( ) returns 3.
SEARCH("S*A", [ProductCode])
The following example uses SEARCH( ) in conjunction with the LEFT( ) function to display the characters that precede the first space character in a product name. The LEFT( ) function extracts a substring of a specified length, starting from the first character. In this example, the length of the substring to display is equal to the numerical position of the space character.
LEFT([ProductName], SEARCH(" ", [ProductName]))
If the product name is 1969 Ford Falcon, the expression returns 1969.
SQRT( )
Calculates the square root of a number.
Syntax
SQRT(number)
Argument
number
The number for which you want to find the square root. The number must be a positive number.
Returns
A number that is the square root of the specified number.
Examples
The following example calculates the square root of each numeric value in the LotSize field:
SQRT([LotSize])
The following example uses SQRT( ) to calculate the actual distance travelled uphill, given the base distance and elevation values. This example applies the Pythagorean theorem, which states that . Using this theorem, the actual distance traveled is c, which means we want to calculate:
Returns the current date that includes a time value of midnight, 12:00 AM.
Syntax
TODAY( )
Returns
The current date in the following format:
Feb 11, 2010 12:00 AM
Examples
The following example calculates the number of days from the current date to Christmas:
DIFF_DAY(TODAY(), "12/25/10")
The following example calculates the number of years from each value in the HireDate field to the current date:
DIFF_YEAR([HireDate], TODAY())
TRIM( )
Removes the leading and trailing blanks from a specified string. TRIM( ) does not remove blank characters between words.
Syntax
TRIM(source)
Argument
source
The string from which to remove leading and trailing blank characters.
Returns
A string with all leading and trailing blank characters removed.
Example
The following example uses TRIM( ) to remove all leading and trailing blank characters from values in the FirstName and LastName fields. The expression uses the & operator to concatenate each trimmed FirstName value with a space, then with each trimmed LastName value.
TRIM([FirstName]) & " " & TRIM([LastName])
TRIMLEFT( )
Removes the leading blanks from a specified string.
Syntax
TRIMLEFT(source)
Argument
source
The string from which to remove the leading blank characters.
Returns
A string with all leading blank characters removed.
Example
The following example concatenates a literal string with each value in the customerName field. TRIMLEFT( ) removes all blank characters preceding the customerName value so that there are no extra blank characters between the literal string and the customerName value.
"Customer name: " & TRIMLEFT([customerName])
TRIMRIGHT( )
Removes the trailing blanks from a specified string.
Syntax
TRIMRIGHT(source)
Argument
source
The string from which to remove the trailing blank characters.
Returns
A string with all trailing blank characters removed.
Example
The following example concatenates each value in the Comment field with a semicolon, then with a value in the Action field. TRIMRIGHT( ) removes all blank characters after the Comment value so that there are no extra blank characters between the Comment string and the semicolon.
TRIMRIGHT([Comment]) & "; " & [Action]
UPPER( )
Converts all letters in a string to uppercase.
Syntax
UPPER(source)
Argument
source
The string to convert to uppercase.
Returns
The specified string in all uppercase letters.
Example
The following example displays all the string values in the customerName field in all uppercase:
UPPER([customerName])
WEEK( )
Returns a number from 1 to 52 that represents the week of the year.
Syntax
WEEK(date)
Argument
date
The date or date expression whose week of the year to get.
Returns
A number that represents the week of the year for the specified date value.
Example
The following example gets the week number of the year for each date value in the ShipDate field:
WEEK([ShipDate])
WEEKDAY( )
Returns the day of the week for a specified date value.
Syntax
WEEKDAY(date, option)
Arguments
date
The date or date expression from which you want to get the day of the week.
option
A number that represents the weekday format to return. Use one of the following values:
1 to get the day as a number from 1 (Sunday) to 7 (Saturday).
2 to get the day as a number from 1 (Monday) to 7 (Sunday).
3 to get the day as a number from 0 (Monday) to 6 (Sunday).
4 to get the full weekday name, for example, Wednesday. The result is locale‑specific.
5 to get the abbreviated weekday name, for example Wed. The result is locale‑specific.
If you omit option, WEEKDAY( ) assumes option 1.
Returns
The day of the week for a specified date value.
Example
The following example gets the full weekday name for each date value in the DateSold field:
WEEKDAY([DateSold], 4)
YEAR( )
Returns the four‑digit year value for a specified date value.
Syntax
YEAR(date)
date
The date or date expression from which you want to extract the year part.
Returns
The number that represents the four‑digit year for the specified date value.
Example
The following example gets the four‑digit year for each date value in the ShipDate field, and adds 15 to the four‑digit year. For example, if the ShipDate value is Sep 16, 2008, YEAR( ) returns 2023.
If you omit n, the number of characters to extract, the function returns the first character only. 
. Using this theorem, the actual distance traveled is c, which means we want to calculate: