Functions used in computed column expressions

For example, compare a cross tab revenue value to the grand total of all revenue values, using the following calculation:

(<cross tab revenue value>/<cross tab revenue grand total>) * 100%

In Measure, select the sales revenue measure. Interactive Crosstabs displays a percentage value in the cell next to each sales revenue value. The percentage value equals the sales revenue value divided by the column sum, and multiplied by 100%, as follows:

(<sales revenue value>/<column sum>) * 100%

Interactive Crosstabs calculates the percentage of the difference between the second measure and the first measure, as follows:

((measureB - measureA)/measureA) * 100%

Interactive Crosstabs displays the percentage of the difference in the cross tab cells.

For each measure in each row, Interactive Crosstabs calculates the percentage of the row total and displays the percentage value, as follows:

(<measure value>/<row sum>) * 100%

The sum of percent-of-row values in each row is 100%.

For each measure in each column and row, Interactive Crosstabs calculates the percentage of the cross tab grand total and displays the percent value, as follows:

(<measure value>/<cross tab grand total>) * 100%

The sum of all percent-of-total values in the cross tab is 100%.

num

The number, or numeric expression that specifies the number for which you want to find the absolute value.

The following example returns the absolute value for each number in the TemperatureCelsius data field:

ABS([TemperatureCelsius])

date

The date or date expression that represents the start date.

days

The number of days to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example adds 15 days to each date value in the InvoiceDate data field:

ADD_DAY([InvoiceDate], 15)

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.

hoursToAdd

The number of hours to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example adds eight hours to each date value in the ShipDate data field:

ADD_HOUR([ShipDate], 8)

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.

minutesToAdd

The number of minutes to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example subtracts 30 minutes from each date in the StartTime data field:

ADD_MINUTE([StartTime], -30)

date

The date or date expression that represents the start date.

monthsToAdd

The number of months to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example adds two months to each date value in the InitialRelease data field:

ADD_MONTH([InitialRelease], 2)

date

The date or date expression that represents the start date.

quartersToAdd

The number of quarters to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example adds two quarters to each date value in the ForecastClosing data field:

ADD_QUARTER([ForecastClosing], 2)

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.

secondsToAdd

The number of seconds to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The date-and-time value that results from adding the specified number of seconds to the start date.

The following example adds 30 seconds to each date value in the StartTime data field:

ADD_SECOND([StartTime], 30)

date

The date or date expression that represents the start date.

weeksToAdd

The number of weeks to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example adds two weeks to each date value in the OrderDate data field:

ADD_WEEK([OrderDate], 2)

date

The date or date expression that represents the start date.

yearsToAdd

The number of years to add to the start date. If you specify a negative number, the result appears to subtract the number from the start date.

The following example adds five years to each date value in the HireDate data field:

ADD_YEAR([HireDate], 5)

value

The value to test. The value can be a string, numeric, or date value.

upperBound

The first value in the range of values to which to compare. String and date values must be enclosed in double quotation marks (" ").

lowerBound

The second value in the range of values to which to compare. String and date values must be enclosed in double quotation marks (" ").

The following example tests each value in the SalesTotal data field to see if the value is between 10000 and 20000:

BETWEEN([SalesTotal], 10000, 20000)

The following example tests each value in the CustomerName data field to see if the value is between A and M:

BETWEEN([CustomerName], "A", "M")

The following example tests each value in the ReceiptDate data field to see if the value is between 10/01/07 and 12/31/07:

BETWEEN([ReceiptDate], "10/01/07 12:00 AM", "12/31/07 12:00 AM")

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.

IF((BETWEEN([orderDate], "12/1/07 12:00 AM", "12/31/07 12:00 AM")), (ADD_DAY([orderDate], 5)), (ADD_DAY([orderDate], 3)))

num

The numeric value to round up.

significance

The multiple up to which to round num.

CEILING( ) is commonly used to round up prices. For example, to avoid dealing with pennies, if the Price value is 20.52, CEILING( ) returns 20.55. You can round prices in a Price data field up to the nearest nickel with the following expression:

CEILING([Price], 0.05)

If the Price value is 20.52, CEILING( ) returns 20.60. If the Price value is 20.40, CEILING( ) returns 20.40. No rounding occurs because 20.40 is already a multiple of 0.1. The following example rounds prices up to the nearest dime:

CEILING([Price], 0.1)

The following example rounds prices up to the nearest dollar. If the Price value is 20.30, CEILING( ) returns 21.0.

CEILING([Price], 1)

date

The date or date expression from which you want to extract the day.

The following example gets the number of the day for each date value in the ShipDate data field:

DAY([ShipDate])

date1

The first date or date expression to use in the calculation.

date2

The second date or date expression to use in the calculation.

The following example calculates the number of days between each value in the invoiceDate data field and each value in the paymentDate data field:

DIFF_DAY([invoiceDate],[paymentDate])

The results show how long it takes to pay invoices. The following example calculates the number of days from an order date to Christmas:

DIFF_DAY([orderDate], "12/25/08")

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/08")

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.

The following example calculates the number of hours between each value in the startTime data field and each value in the finishTime data 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. If you supply a literal date as an argument, you must include the time value, as shown in the following example:

DIFF_HOUR(NOW(), "12/25/08 12:00 AM")

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.

The following example calculates the number of minutes between each value in the startTime data field and each value in the finishTime data 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. If you supply a literal date as an argument, you must include the time value, as shown in the following example:

DIFF_MINUTE(NOW(), "12/25/08 12:00 AM")

date1

The first date or date expression to use in the calculation.

date2

The second date or date expression to use in the calculation

The following example calculates the number of months between each value in the askByDate data field and each value in the ShipByDate data field:

DIFF_MONTH([askByDate],[shipByDate])

The following example calculates the number of months from each value in the hireDate data field to the end of the year:

DIFF_MONTH([hireDate], "1/1/09")

date1

The first date or date expression to use in the calculation.

date2

The second date or date expression to use in the calculation.

The following example calculates the number of quarters between each value in the PlanClosing data field and each value in the ActualClosing data field:

DIFF_QUARTER([PlanClosing],[ActualClosing])

The following example calculates the number of quarters from each value in the orderDate data field to the end of the year:

DIFF_QUARTER([orderDate], "1/1/09")

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.

The following example calculates the number of seconds between each value in the startTime data field and each value in the finishTime data 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. If you supply a literal date as an argument, you must include the time value, as shown in the following example:

DIFF_SECOND(NOW(), "12/24/08 12:00 AM")

date1

The first date or date expression to use in the calculation.

date2

The second date or date expression to use in the calculation.

The following example calculates the number of weeks between each value in the askByDate data field and each value in the shipByDate data field:

DIFF_WEEK([askByDate],[shipByDate])

The following example calculates the number of weeks from each value in the orderDate data field to the end of the year:

DIFF_WEEK([orderDate], "1/1/09")

date1

The first date or date expression to use in the calculation.

date2

The second date or date expression to use in the calculation.

The following example calculates the number of years between each value in the HireDate data field and each value in the TerminationDate data field:

DIFF_YEAR([HireDate],[TerminationDate])

The following example calculates the number of years from each value in the HireDate data field to the current date. TODAY( ) is a function that returns the current date:

DIFF_YEAR([HireDate], TODAY())

FIND(strToFind, str)

FIND(strToFind, str, startPosition)

strToFind

The substring to search for. The search is case-sensitive.

str

The string in which to search.

startPosition

The position in str where the search starts.

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, LEFT( ) returns the following string:

ModelA

condition

The condition to test.

doIfTrue

The value to return if condition evaluates to TRUE.

doIfFalse

The value to return if condition evaluates to FALSE.

The following example calculates and displays different discount amounts based on the value in the Total data 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 2007 (between 12/1/07 and 12/31/07), add five days to the orderDate value. If a orderDate value is in a month other than December, add three days to th e orderDate value.

IF((BETWEEN([orderDate], "12/1/07 12:00 AM", "12/31/07 12:00 AM")), (ADD_DAY([orderDate], 5)), (ADD_DAY([orderDate], 3)))

The following example checks each value in the Office data field. If the value is Boston, San Francisco, or NYC, the computed column displays U.S. If the value is something other than Boston, San Francisco, or NYC, the computed column displays Europe and Asia Pacific.

IF([Office]="Boston" OR [Office]="San Francisco" OR [Office]="NYC", "U.S.","Europe and Asia Pacific")

value

The value to test. The value can be a string, numeric, or date value.

check1, ..., checkN

The value or values to which to compare.

The following example tests if New Haven, Baltimore, or Cooperstown are values in the city data field. If any one of the cities is in the data 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 data field. If you supply a literal date as an argument, you must include the time value, as shown in the following example:

IN([payDate], "9/15/08 12:00 AM", "9/30/08 12:00 AM")

The following example uses IN( ) in conjunction with the IF( ) function to test if Ships or Trains are values in the ProductLine data field. If Ships or Trains is a value in the field, the computed column displays Discontinued Item; otherwise, the product line value is displayed as it appears in the field.

IF(IN([ProductLine], "Ships", "Trains"),"Discontinued Item", [ProductLine])

value

The data field in which to check for null values.

The following example uses ISNULL( ) in conjunction with the IF( ) function to test for null values in the BirthDate data field. If there is a null value, the computed column displays No date specified; otherwise the BirthDate value is displayed.

IF(ISNULL([BirthDate]), "No date specified", [BirthDate])

LEFT(str)

LEFT(str, n)

str

The string from which to extract a substring.

n

The number of characters to extract, starting from the first character.

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.

The following example displays the first letter of each name in the CustomerName data 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, LEFT( ) returns the following string:

ModelA

str

The string expression to evaluate.

The following example returns the length of each value in the ProductCode data 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.

RIGHT( [PRODUCTNAME], LEN([PRODUCTNAME] ) - (FIND("-" , [PRODUCTNAME] )))

If the product code is ModelA-Ford, RIGHT( ) returns the following string:

A-Ford

LIKE(str, pattern)

str

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 Special characters are characters that are neither alphabetic nor numeric. These characters may not be allowed in text because they have programmatic uses. Other terms for special characters include reserved characters, restricted characters, forbidden characters, excluded characters, included 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%"

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 computed column displays U.S. Model; otherwise, it displays Imported Model.

IF(((LIKE([ProductName], "%Ford%") = TRUE) OR (LIKE([ProductName], "%Chevy%") = TRUE)), "U.S. Model", "Imported Model")

str

The string to convert to lowercase.

The following example displays all the string values in the productLine data field in lowercase:

LOWER([productLine])

source

The string to evaluate.

pattern

The string pattern to match. The pattern uses ECMAScript (JavaScript) syntax, as defined in Section 15.10 of Standard ECMA-262.

The following example uses ECMAScript syntax in the pattern to detect any set of characters followed by the letter C, and returns true:

MATCH("ABC",".*C")

The following example checks whether the string starts with the letter X, followed by any single character, and ending with C. It returns false.

MATCH("ABC","X.C")

number

The number from which to derive the mod value.

divisor

The divisor for the mod function.

The following example computes the remainder of PriceEstimate data field divided by 12, returning an integer. For example, if the PriceEstimate value is 27365, MOD( ) returns 5.

MOD([PriceEstimate], 12)

MONTH(date)

MONTH(date, option)

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

The following example returns the month (1 – 12) for each value in the ShipDate data field:

MONTH([ShipDate])

The following example returns the full month name for each ShipDate value:

MONTH([ShipDate], 2)

expression

The Boolean value or expression to negate.

The following example uses NOT( ) in conjunction with the IF( ) and LIKE( ) functions. It tests if the value in the State data field is not CA. If the value is not CA, it returns the value of the Markup data field multiplied by 10%. If the value is CA, it returns the value of the Markup data field multiplied by 15%:

IF(NOT(LIKE([State], "CA")),[Markup]*10%,[Markup]*15%)

The previous IF( ) statement is semantically equivalent to the following statement:

IF(LIKE([State], "CA"),[Markup]*15%,[Markup]*10%)

value

The data field in which to check for non-null values.

The following example uses NOTNULL( ) in conjunction with the IF( ) function to test for non-null values in the BirthDate data field. If there is a non-null value, the BirthDate value is displayed; otherwise the string "No date specified" is displayed.

IF(NOTNULL([BirthDate]), [BirthDate], "No date specified")

The current date and time. For example:

Sep 23, 2008 11:56 AM

The following example uses the DIFF_MINUTE( ) and NOW( ) functions to calculate the number of minutes from the current date and time to Christmas:

DIFF_MINUTE(NOW(), "12/25/08 12:00 AM")

date

The date or date expression whose quarter number to get.

The following example displays the quarter number for each value in the CloseDate data field:

QUARTER([CloseDate])

The following example displays a string—Q1, Q2, Q3, or Q4—for each value in the CloseDate data field:

"Q" & QUARTER([CloseDate])

expr

The expression that specifies the values to evaluate. The field can be of string, numeric, or date type.

ascending

Use one of the following values:

• 0 to rank values in descending order. In descending order, the highest value is ranked 1, and the lowest value is equal to the number of values in the set.
• 1 to rank values in ascending order. In ascending order, the lowest value is ranked 1, and the highest value is equal to the number of values in the set.

If you omit the ascending argument, RANK( ) assumes 0 (descending order).

groupLevel

The numeric index of the group whose values to use in the calculation. 0 indicates the table, 1 indicates the first group, 2 indicates the second group, and so on. If you do not supply a value for groupLevel, the function performs the calculation over all the values in the table.

The following example ranks each value in the Score data field. The ranking is performed over all the values in the table.

RANK([Score])

The following example ranks each value in the Score data field in ascending order. The ranking is performed over all the values in each group at the second level.

RANK([Score], 1, 2)

RIGHT(str) R

RIGHT(str, n)

str

The string from which to extract a substring.

n

The number of characters to extract, starting from the last character.

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.

The following example displays the last four characters of each value in the ProductCode data 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.

RIGHT([ProductCode], (LEN([ProductCode]) - FIND("-" , [ProductCode])))

If the product code is ModelA-Ford, RIGHT( ) returns Ford. If the product code is ModelBToyota, RIGHT( ) returns Toyota.

ROUND(num)

ROUND(num, dec)

num

The number to round.

dec

The number of digits up to which to round num. If you omit dec, ROUND( ) assumes 0.

The following example rounds the numbers in the PriceEstimate data 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 data 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 data 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(num)

ROUNDDOWN(num, dec)

num

The number to round down.

dec

The number of digits up to which to round num down. If you omit dec, ROUND( ) assumes 0.

The following example rounds down the numbers in the PriceEstimate data 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 data 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 data 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(num)

ROUNDUP(num, dec)

num

The number to round up.

dec

The number of digits up to which to round num up. If you omit dec, ROUND( ) assumes 0.

The following example rounds up the numbers in the PriceEstimate data 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 data 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 data 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)

expr

The expression that specifies the values to use in the calculation. The data type must be numeric.

The following example calculates a running sum for each value in the LineItemTotal. For example, if the LineItemTotal field contains 50, 75, 80, 90, and 95, RUNNINGSUM( ) returns 50, 125, 205, 295, and 390 for each row, respectively.

RUNNINGSUM([LineItemTotal])

SEARCH(pattern, str)

SEARCH(pattern, str, startPosition)

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.

str

The string in which to search.

startPosition

The position in str where the search starts.

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.

num

The number, or numeric expression that specifies the number, for which you want to find the square root. The number must be a positive number.

The following example calculates the square root of each value in the LotSize data field:

SQRT([LotSize])

The current date in the following format:

Sep 25, 2008 12:00 AM

The following example calculates the number of days from the current date to Christmas:

DIFF_DAY(TODAY(), "12/25/08")

The following example calculates the number of years from each value in the HireDate data field to the current date:

DIFF_YEAR([HireDate], TODAY())

str

The string from which to remove leading and trailing blank characters.

The following example uses TRIM( ) to remove all leading and trailing blank characters from values in the FirstName and LastName data 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])

str

The string from which to remove the leading blank characters.

The following example concatenates a literal string with each value in the customerName data 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])

str

The string from which to remove the trailing blank characters.

The following example concatenates each value in the Comment data field with a semicolon, then with a value in the Action data 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]

str

The string to convert to uppercase.

The following example displays all the string values in the customerName data field in all uppercase:

UPPER([customerName])

date

The date or date expression whose week of the year to get.

The following example gets the week number of the year for each date value in the ShipDate data field:

WEEK([ShipDate])

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.

The following example gets the full weekday name for each date value in the DateSold data field:

WEEKDAY([DateSold], 4)

date

The date or date expression from which you want to extract the year part.

The following example gets the four-digit year for each date value in the ShipDate data field, and adds 15 to the four-digit year. For example, if the ShipDate value is Sep 16, 2008, YEAR( ) returns 2023.

(YEAR([ShipDate]) + 15)