Functions
The following table describes the built-in functions, which accept one or more parameters and return a single
value. Double-quotes are used around string parameters and tokens that contain date/time values. In the Syntax column below, parameters in square brackets are optional.
| Function | Description | Syntax | Notes |
|---|---|---|---|
Abs
| Returns the absolute value of a number. | Abs(number) | Abs(-5) = 5 |
| CXMLDate | Converts date in ISO format (such as those returned from SQL Server) into compatible format for manipulation by built-in functions. |
CXMLDate("date value")
Usage example: If myDate = 2014-10-02T13:30:00 then CXMLDate("@Data.myDate~") returns "10/2/2014 13:30:00" |
ISO 8601 format is 2014-05-31T13:30:00 representing yyyy-mm-ddThh:mm:ss See Special Functions and Attributes. |
| Date | Returns the current date. | Date() | |
| DateAdd | Adds or subtracts an interval of time from a date or time; returns a date value |
DateAdd("interval", number, "date value")
Usage examples: where Posted = "10/21/2014", DateAdd("d", 7, "@Data.Posted~") returns "10/28/2014"DateAdd("d", -7, "@Data.Posted~") returns "10/14/2014" | Interval may be: |
| DateDiff | Returns the difference between two dates. | DateDiff("interval", "date value 1", "date value 2")
Usage example: where StartDate= "10/02/2014" and EndDate = "11/02/2014", DateDiff("d","@Data.StartDate~", "@Data.EndDate~") returns 30 | For valid interval values, see DateAdd function |
| DatePart | Returns a part of a date. | DatePart("interval", "date value" [, FirstDayofWeek [, FirstWeekofYear]] ) Usage example: where myDate = "10/02/2104", DatePart("m", "@Data.myDate~") returns 10 |
For valid interval values, see DateAdd function.
FirstDayofWeek may be: 0 = Use SystemDayOfWeek 1 = Sunday (default) 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = SaturdayFirstWeekofYear may be: 0 = Use System 1 = Week in which Jan 1 occurs (default) 2 = The first week with at least four days in new year 3 = The first full week of the new year |
| DateSerial | Combines date parts together to make a complete date. |
DateSerial(year, month, day)
Usage example: where myYear = 2014, DateSerial(@Data.myYear~, 10, 2) returns "10/2/2014" | |
| DateValue | Returns a valid date-type value created from a text-type argument. Can convert text dates in many different formats. |
DateValue("date string")
Usage examples: where myDate = "2014-10-2"), DateValue("@Data.myDate") - or - DateValue("2-Oct-2014") - or - DateValue("October 2, 2014") returns "10/2/2014" | |
| Day | Returns the day of the month. | Day(date) | Possible return values are from 1-31. |
| Exp | Returns "e" raised to a power. "e" is the base of natural logarithms, called the antilogarithm | Exp(number) | |
| FormatCurrency | Formats a number value into currency | FormatCurrency(number [, NumDigitsAfterDecimal [, IncludeLeadingDigit [, UseParensForNegativeNumbers [, GroupDigits ]]]]) | |
| FormatDateTime | Formats a date-time value. | FormatDateTime(date [, NamedFormat]) | NamedFormat may be vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, or vbShortTime |
| FormatNumber | Formats a number. | FormatNumber(number [, NumDigitsAfterDecimal [, IncludeLeadingDigit [, UseParensForNegativeNumbers [, GroupDigits ]]]]) | |
| FormatPercent | Formats a number as a percentage. | FormatPercent(number [, NumDigitsAfterDecimal [, IncludeLeadingDigit [, UseParensForNegativeNumbers [, GroupDigits ]]]]) | |
| Hour | Returns the hour of the day | Hour(time) | Possible return values are 0-23. |
| IIF | Returns one value if the expression evaluates True, another value if False. Can be nested./ | IIF(expression, true-value, false-value | expression is a formula that returns True or False. Example: this returns "Blue": IIF(1=2,"Red","Blue") See Special Functions and Attributes. |
| InStr | Returns the starting character position where one string is found within another string. | InStr([start, ] string1, string2 [, compare]) | string1 is string to search, string2 is string to search for. Returns 0 when the string is not found. The first characters is at position 1. Set compare to 1 for case-insensitive searches. |
| InStrRev | Same as InStr( ), but search begins from end. | InStrRev(string1, string2 [, start, ] [, compare]) | See Instr( ) |
| Int | Returns the integer portion of a number, removing any decimal places. | Int(number) | |
| IsDate | Returns True if the text is a date. | IsDate(text) | |
| IsNumeric | Returns True if the text is a number. | IsNumeric(text) | |
| LCase | Converts all characters to lower case. | LCase(text) | |
| Left | Returns x characters from text, starting from left. | Left(text, x) | |
| Len | Returns the number of characters in the text. | Len(text) | |
| LTrim | Removes any spaces from left end of text. | LTrim(text) | |
| Mid | Returns sub-string of characters from the middle of the text. | Mid(text, start [, length]) | start is the position of first character to be returned. The first character of the entire text is at position 1. length is the number of characters to be returned. |
| Minute | Returns the minute of the hour. | Minute(time) | Possible return values are 0-59. |
| Month | Returns the month of the year. | Month(date) | Possible return values are 1-12. |
| MonthName | Returns the name of the month. | MonthName(month# [, abbreviate]) | Set abbreviate to True for an abbreviated month name. |
| Now | Returns the current date and time. | Now() | |
| Replace | Replaces instances of text with other text. | Replace( text, textFind, textReplaceWith [, start [, count [, compare ]]]) | text is the original text, textFind is the text to be replaced, textReplaceWith is the replacement text
start is the starting character position to be searched count is the maximum number of replacements before stopping. Set compare to 1 to replace characters regardless of case. Example: Replace("ABC", "abc", "123", 1, 1) produces "123". |
| Right | Returns x characters from text, starting from right. | Right(text, x) | |
| Rnd | Returns a random number between 0 and 1. | Rnd([number]) |
If number is: not supplied - returns the next random number in the sequence < 0 - returns the same number every time > 0 - returns the next random number in the sequence = 0 - returns the most recently generated number |
| Round | Returns a number rounded to a specified number of decimal places. | Round(expression [, numdecimalplaces]) | |
| RTrim | Removes any trailing spaces from right end of text. | RTrim(text) | |
| Second | Returns the second of the minute. | Second(time) | Possible return values are 0-59. |
| Sgn | Returns indication of number's sign. | Sgn(number) | Returns: -1 if the number is negative, 1 if the number is positive, 0 if the number is 0. |
| Space | Returns a string consisting of the designated number of spaces. | Space(number) | |
| Sqr | Returns the square root of a number. | Sqr(number) | |
| String | Returns text consisting of the character duplicated x number of times. | String(x, "character") | |
| StrReverse | Returns the text with the characters in reverse order. | StrReverse(text) | |
| TimeValue | Returns a valid time-type value created from a text-type argument. Can convert text dates in many different formats. | TimeValue(time) | |
| Trim | Removes both leading and trailing spaces from the text. | Trim(text) | |
| UCase | Converts all characters to upper case. | UCase(text) | |
| Weekday | Returns the number of the day of the week. | Weekday(date [,firstdayofweek ]) | Possible return values are 1-7. The first day of the week defaults to 1 = Sunday, but the optional argument can be used to set it differently: 2 = Monday, 3 = Tuesday, etc. |
| WeekdayName | Returns the name of the day corresponding to the weekday number. | WeekdayName(numberWeekday, abbreviate, firstdayofweek) | |
| Year | Returns the number of the year of the specified date. | Year(date) |