Expressions allow you to dynamically assign or modify the content displayed in a cell based on specific conditions or criteria. They enable you to evaluate the data in a cell and determine the appropriate value or caption to be shown, applying predefined logic or transformations. These expressions are useful for defining new captions, performing calculations, or adjusting content according to different conditions. Exontrol's eXPression component provides a syntax editor that helps you define, view, edit, and evaluate expressions. It allows you to easily check the correctness of your expressions and test their results with different values, making it an ideal tool for configuring applications and ensuring the proper execution of expressions.

Usage examples:

• "100 + 200", adds two numbers and returns 300
• "`100` + 200", concatenates the strings, returning "100200"
• "currency(1000)", displays the value in currency format based on the current regional setting, such as `$1,000.00` for the US format
• "1000 format ``" , displays "1,000.00" for English format, while "1.000,00" is displayed for the German format
• "1000 format 2|.|3|", always displays "1,000.00", regardless of the control panel settings
• "value format ``" , displays the numeric value according to the current regional settings
• "date(value) format `MMM d, yyyy`", returns the date in the format "Sep 2, 2023" for English
• "upper(`string`)", converts the given string to uppercase letters, resulting in "STRING"
• "date(dateS('3/1/' + year(9:=#1/1/2018#)) + ((1:=(((255 - 11 * (year(=:9) mod 19)) - 21) mod 30) + 21) + (=:1 > 48 ? -1 : 0) + 6 - ((year(=:9) + int(year(=:9) / 4)) + =:1 + (=:1 > 48 ? -1 : 0) + 1) mod 7))", returns the date when Easter Sunday will fall in 2018. The expression returns #4/1/2018# for this year. If #1/1/2018# is replaced with #1/1/2019#, it returns #4/21/2019#

The general-expression supports:

• Constants and keywords: 0, 1.2, value, %0, etc
• Arithmetic operators: +, -, *, etc.
• Boolean operators: <, <=, =, etc.
• Range operators: MIN, MAX
• Store/restore operators: :=, =:
• Conversion operators: str, dbl, date, etc.
• Bitwise operators for numbers: bitand, bitor, etc.
• Operators for numbers: int, round, floor, etc.
• Operators for strings: len, lower, upper, etc.
• Operators for dates: year, month, day, etc.
• Miscellaneous operators: ? (immediate IF), case, switch, in, etc.

The following is a detailed explanation of all the predefined constants, operators, and functions supported by the general expression.

The constants can be represented as:

• numbers in decimal format ( where dot character specifies the decimal separator ). For instance: -1, 100, 20.45, .99 and so on
• numbers in hexa-decimal format ( preceded by 0x or 0X sequence ), uses sixteen distinct symbols, most often the symbols 0-9 to represent values zero to nine, and A, B, C, D, E, F (or alternatively a, b, c, d, e, f) to represent values ten to fifteen. Hexadecimal numerals are widely used by computer system designers and programmers. As each hexadecimal digit represents four binary digits (bits), it allows a more human-friendly representation of binary-coded values. For instance, 0xFF, 0x00FF00, and so so.
• date-time in format #mm/dd/yyyy hh:mm:ss#, For instance, #1/31/2001 10:00# means the January 31th, 2001, 10:00 AM
• string, if it starts / ends with any of the ' or ` or " characters. If you require the starting character inside the string, it should be escaped ( preceded by a \ character ). For instance, `Mihai`, "Filimon", 'has', "\"a quote\"", and so on

The predefined constants are:

• bias ( BIAS constant), defines the difference, in minutes, between Coordinated Universal Time (UTC) and local time. For example, Middle European Time (MET, GMT+01:00) has a time zone bias of "-60" because it is one hour ahead of UTC. Pacific Standard Time (PST, GMT-08:00) has a time zone bias of "+480" because it is eight hours behind UTC. For instance, date(value - bias/24/60) converts the UTC time to local time, or date(date('now') + bias/24/60) converts the current local time to UTC time. For instance, "date(value - bias/24/60)" converts the value date-time from UTC to local time, while "date(value + bias/24/60)" converts the local-time to UTC time.
• dpi ( DPI constant ), specifies the current DPI setting. and it indicates the minimum value between dpix and dpiy constants. For instance, if current DPI setting is 100%, the dpi constant returns 1, if 150% it returns 1.5, and so on. For instance, the expression value * dpi returns the value if the DPI setting is 100%, or value * 1.5 in case, the DPI setting is 150%
• dpix ( DPIX constant ), specifies the current DPI setting on x-scale. For instance, if current DPI setting is 100%, the dpix constant returns 1, if 150% it returns 1.5, and so on. For instance, the expression value * dpix returns the value if the DPI setting is 100%, or value * 1.5 in case, the DPI setting is 150%
• dpiy ( DPIY constant ), specifies the current DPI setting on x-scale. For instance, if current DPI setting is 100%, the dpiy constant returns 1, if 150% it returns 1.5, and so on. For instance, the expression value * dpiy returns the value if the DPI setting is 100%, or value * 1.5 in case, the DPI setting is 150%
• eol, represents the end-of-line sequence ("\r\n" or chr(13) & chr(10))
• tab, corresponds to the tab character (chr(9))

The supported binary arithmetic operators are:

• * ( multiplication operator ), priority 5, multiplies two numeric values. For instance, the expression value * 2 doubles the value
• / ( division operator ), priority 5, divides one numeric value by another and returns the quotient as a floating-point (decimal) result. For instance, the expression value / 2 divides the given value by 2
• mod ( reminder operator ), priority 5, returns the remainder after dividing one number by another. For instance, the expression value mod 2 returns the remainder when value is divided by 2
• + ( addition operator ), priority 4, performs numeric addition when both operands are numbers or concatenates them if at least one is a string. For instance, the expression  `EUR` + value prefixes the value with the "EUR" string
• - ( subtraction operator ), priority 4, performs numeric subtraction when both operands are numbers. It calculates the difference between the two numbers. For instance, the expression value - 3 subtracts 3 from the value.

The supported unary boolean operators are:

• not ( not operator ), priority 3 ( high priority ), inverts the boolean value of its operand. If the operand is True, it returns False, and if the operand is False, it returns True. For instance, the expression not value inverts the boolean value of value. If value is True, the result will be False, and vice versa.

The supported binary boolean operators are:

• or ( or operator ), priority 2, returns True if at least one of its operands is True. If both operands are False, it returns False. For instance, the expression (value = 1) Or (value = 2) checks if value is either 1 or 2. It returns True if value is equal to 1 or 2, and False if value is neither
• and ( or operator ), priority 1, returns True only if both of its operands are non-zero (i.e., True in a boolean context). If either operand is zero (i.e., False), the result will be False. For instance, the expression (value > 5) And (value < 20) checks whether the value is greater than 5 and less than 20 at the same time.

The supported binary boolean operators, all these with the same priority 0, are :

• < ( less operator ), compares two values and returns True if the left value is less than the right value. For instance, the expression value < 10 returns True if value is less than 10.
• <= ( less or equal operator ), compares two values and returns True if the left value is less than or equal to the right value. For instance, the expression value <= 10 returns True if value is less than or equal to 10.
• = ( equal operator ), compares two values and returns True if the left value is equal to the right value. For instance, the expression value = 10 returns True if value is equal to 10.
• != ( not equal operator ), compares two values and returns True if the left value is not equal to the right value. For instance, the expression value != 10 returns True if value is not equal to 10.
• >= ( greater or equal operator ), compares two values and returns True if the left value is greater than or equal to the right value. For instance, the expression value >= 10 returns True if value is greater than or equal to 10.
• > ( greater operator ), compares two values and returns True if the left value is greater than the right value. For instance, the expression value > 10 returns True if value is greater than 10.

The supported binary range operators, all these with the same priority 5, are :

• a MIN b ( min operator ), indicates the minimum value, so a MIN b returns the value of a, if it is less than b, else it returns b. For instance, the expression value MIN 10 returns always a value greater than 10.
• a MAX b ( max operator ), indicates the maximum value, so a MAX b returns the value of a, if it is greater than b, else it returns b. For instance, the expression value MAX 100 returns always a value less than 100.

The supported binary operators, all these with the same priority 0, are :

• := (Store operator),  stores the result of expression to variable. The syntax for := operator is 

  variable := expression

  where variable is a integer between 0 and 9. You can use the =: operator to restore any stored variable ( please make the difference between := and =: ). For instance, (0:=dbl(value)) = 0 ? "zero" : =:0, stores the value converted to double, and prints zero if it is 0, else the converted number. Please pay attention that the := and =: are two distinct operators, the first for storing the result into a variable, while the second for restoring the variable

• =: (Restore operator),  restores the giving variable ( previously saved using the store operator ). The syntax for =: operator is 

  =: variable

  where variable is a integer between 0 and 9. You can use the := operator to store the value of any expression ( please make the difference between := and =: ). For instance, (0:=dbl(value)) = 0 ? "zero" : =:0, stores the value converted to double, and prints zero if it is 0, else the converted number. Please pay attention that the := and =: are two distinct operators, the first for storing the result into a variable, while the second for restoring the variable

The supported conversion operators are:

• type (unary operator) retrieves the type of the object. The type operator may return any of the following: 0 - empty ( not initialized ), 1 - null, 2 - short, 3 - long, 4 - float, 5 - double, 6 - currency, 7 - date, 8 - string, 9 - object, 10 - error, 11 - boolean, 12 - variant, 13 - any, 14 - decimal, 16 - char, 17 - byte, 18 - unsigned short, 19 - unsigned long, 20 - long on 64 bits, 21 - unsigned long on 64 bites. For instance type(%1) = 8 specifies the cells ( on the column with the index 1 ) that contains string values. 
• str (unary operator) converts the expression to a string. The str operator converts the expression to a string. For instance, the str(-12.54) returns the string "-12.54".
• dbl (unary operator) converts the expression to a number. The dbl operator converts the expression to a number. For instance, the dbl("12.54") returns 12.54
• date (unary operator) converts the expression to a date, based on your regional settings. For instance, the date(``) gets the current date ( no time included ), the date(`now`) gets the current date-time, while the date("01/01/2001") returns #1/1/2001#
• dateS (unary operator) converts the string expression to a date using the format MM/DD/YYYY HH:MM:SS. For instance, the dateS("01/01/2001 14:00:00") returns #1/1/2001 14:00:00#
• hex (unary operator) converts the giving string from hexa-representation to a numeric value, or converts the giving numeric value to hexa-representation as string. For instance, hex(`FF`) returns 255, while the hex(255) or hex(0xFF) returns the `FF` string. The hex(hex(`FFFFFFFF`)) always returns `FFFFFFFF` string, as the second hex call converts the giving string to a number, and the first hex call converts the returned number to string representation (hexa-representation).

The bitwise operators for numbers are:

• a bitand b (binary operator) computes the AND operation on bits of a and b, and returns the unsigned value. For instance, 0x01001000 bitand 0x10111000 returns 0x00001000.
• a bitor b (binary operator) computes the OR operation on bits of a and b, and returns the unsigned value. For instance, 0x01001000 bitor 0x10111000 returns 0x11111000.
• a bitxor b (binary operator) computes the XOR ( exclusive-OR ) operation on bits of a and b, and returns the unsigned value. For instance, 0x01110010 bitxor 0x10101010 returns 0x11011000.
• a bitshift (b) (binary operator) shifts every bit of a value to the left if b is negative, or to the right if b is positive, for b times, and returns the unsigned value. For instance, 128 bitshift 1 returns 64 ( dividing by 2 ) or 128 bitshift (-1) returns 256 ( multiplying by 2 )
• bitnot ( unary operator ) flips every bit of x, and returns the unsigned value. For instance, bitnot(0x00FF0000) returns 0xFF00FFFF.

The operators for numbers are:

• int (unary operator) retrieves the integer part of the number. For instance, the int(12.54) returns 12
• round (unary operator) rounds the number ie 1.2 gets 1, since 1.8 gets 2. For instance, the round(12.54) returns 13
• floor (unary operator) returns the largest number with no fraction part that is not greater than the value of its argument. For instance, the floor(12.54) returns 12
• abs (unary operator) retrieves the absolute part of the number ie -1 gets 1, 2 gets 2. For instance, the abs(-12.54) returns 12.54
• sin (unary operator) returns the sine of an angle of x radians. For instance, the sin(3.14) returns 0.001593.
• cos (unary operator) returns the cosine of an angle of x radians. For instance, the cos(3.14) returns -0.999999.
• asin (unary operator) returns the principal value of the arc sine of x, expressed in radians. For instance, the 2*asin(1) returns the value of PI.
• acos (unary operator) returns the principal value of the arc cosine of x, expressed in radians. For instance, the 2*acos(0) returns the value of PI
• sqrt (unary operator) returns the square root of x. For instance, the sqrt(81) returns 9.
• currency (unary operator) formats the giving number as a currency string, as indicated by the control panel. For instance, currency(value) displays the value using the current format for the currency ie, 1000 gets displayed as $1,000.00, for US format.
• value format 'flags' (binary operator) can format a numeric value with specified flags (e.g. 1,230.00), in scientific notation (e.g. 1.23e+03) or fixed-point notation (e.g. 1230.00). The format method formats numeric or date expressions (depends on the type of the value, explained at operators for dates). If the flags is empty, the number is displayed as shown in the field "Number" in the "Regional and Language Options" from the Control Panel.

  A) The 'flags' parameter for the format operator can accept a list of values separated by the | character (e.g., 'NumDigits|DecimalSep|Grouping|ThousandSep|NegativeOrder|LeadingZero'). These flags allow customization of numeric formatting (e.g. 1,230.00), with each representing a specific setting:

  • NumDigits - specifies the number of fractional digits, If the flag is missing, the field "No. of digits after decimal" from "Regional and Language Options" is using.
  • DecimalSep - specifies the decimal separator. If the flag is missing, the field "Decimal symbol" from "Regional and Language Options" is using.
  • Grouping - indicates the number of digits in each group of numbers to the left of the decimal separator. Values in the range 0 through 9 and 32 are valid. The most significant grouping digit indicates the number of digits in the least significant group immediately to the left of the decimal separator. Each subsequent grouping digit indicates the next significant group of digits to the left of the previous group. If the last value supplied is not 0, the remaining groups repeat the last group. Typical examples of settings for this member are: 0 to group digits as in 123456789.00; 3 to group digits as in 123,456,789.00; and 32 to group digits as in 12,34,56,789.00. If the flag is missing, the field "Digit grouping" from "Regional and Language Options" indicates the grouping flag.
  • ThousandSep - specifies the thousand separator. If the flag is missing, the field "Digit grouping symbol" from "Regional and Language Options" is using.
  • NegativeOrder - indicates the negative number mode. If the flag is missing, the field "Negative number format" from "Regional and Language Options" is using. The valid values are 0, 1, 2, 3 and 4 with the following meanings:
    • 0 - Left parenthesis, number, right parenthesis; for example, (1.1) 
    • 1 - Negative sign, number; for example, -1.1
    • 2 - Negative sign, space, number; for example, - 1.1
    • 3 - Number, negative sign; for example, 1.1-
    • 4 - Number, space, negative sign; for example, 1.1 -
  • LeadingZero - indicates if leading zeros should be used in decimal fields.  If the flag is missing, the field "Display leading zeros" from "Regional and Language Options" is using. The valid values are 0, 1

  For instance:

  1. "1000 format ''", displays 1,000.00 for English format, while 1.000,00 is displayed for German format
  2. "1000 format '2|.|3|,'" always renders as 1,000.00, ignoring control panel settings
  3. "dbl(value) format `0`", converts the value to a number and displays with no decimals as 1,000 for English format, while 1.000 is displayed for German format

  If formatting the number fails for some invalid parameter, the value is displayed with no formatting.

  B) The 'flags' property for the format operator can follow the format '%'<flags>?<width>?<precision>?<length>?<specifier>, allowing numeric values to be formatted in either scientific notation (e.g., 1.23e+03) or fixed-point notation (e.g., 1230.00), as detailed below:

  <format> ::= '%' <flags>? <width>? <precision>? <length>? <specifier>
  <flags> ::= <flag>+
  <flag> ::= '-' | '+' | ' ' | '#' | '0'
  <width> ::= <number>
  <precision> ::= '.' <number>
  <length> ::= 'l' | 'L' | 'll' | 'z' | 'j' | 't'
  <specifier> ::= 'e' | 'E' | 'f' | 'F'
  <number> ::= <digit>+
  <digit> ::= '0' | '1' | '2' | '3' | '4' | '5' | '6' | '7' | '8' | '9'

  where:

  • Flags

    Flag	Meaning
    -	Left-justify the result within the field
    +	Always show the sign (+ or -)
    	Leave a space before positive numbers
    #	Force decimal point (%f) or keep trailing zeros (%e)
    0	Pad with leading zeros instead of spaces

  • Width
    Minimum number of characters for the output (e.g., %8.2f uses width 8).

  • Precision
    Specifies digits after the decimal point:
    
    %f: Number of digits after the decimal.
    %e: Digits after the decimal in scientific notation.

  • Length
    Optional and mainly affects how the argument is interpreted:
    
    l: double (default for %f, %e)
    L: long double (e.g., %Lf)
    ll, z, j, t: primarily for integers (ll: Use for large integers (64-bit), , z: Use for sizes or array indices, j: Use for the largest integer type available, t: Use for pointer differences)

  • Specifier

    Specifier	Meaning
    %f	Fixed-point decimal notation
    %F	Same as %f, but with uppercase INF and NAN
    %e	Scientific notation (e.g., 1.23e+05)
    %E	Same as %e, but with uppercase E

  For instance:

  1. "1000 format `%+.2E`", force sign, 2 decimals, uppercase scientific, such as "+1.00E+003"
  2. "dbl(value) format `%-14.4e`", left-aligned, width 14, 4 decimals in scientific, such as "1.0000e+003 "
  3. "value format `%.1E` replace `+00` with `+`", displays the number in scientific notation with 1 digit after the decimal and uppercase 'E' for the exponent, such as "1.0E+3"
  4. "value format `%f`", default fixed-point (6 decimal digits), such as "1000.000000"
  5. "value format `%8.2f`", Width 8, 2 decimal places, such as " 1000.00"

  If number formatting fails due to an invalid parameter, the value is displayed according to the current regional settings.

The operators for strings are:

• len (unary operator) retrieves the number of characters in the string. For instance, the len("Mihai") returns 5.
• lower (unary operator) returns a string expression in lowercase letters. For instance, the lower("MIHAI") returns "mihai"
• upper (unary operator) returns a string expression in uppercase letters. For instance, the upper("mihai") returns "MIHAI"
• proper (unary operator) returns from a character expression a string capitalized as appropriate for proper names. For instance, the proper("mihai") returns "Mihai"
• ltrim (unary operator) removes spaces on the left side of a string. For instance, the ltrim(" mihai") returns "mihai"
• rtrim (unary operator) removes spaces on the right side of a string. For instance, the rtrim("mihai ") returns "mihai"
• trim (unary operator) removes spaces on both sides of a string. For instance, the trim(" mihai ") returns "mihai"
• reverse (unary operator) reverses the order of the characters in the string a. For instance, the reverse("Mihai") returns "iahiM"
• chr (unary operator) returns the character for a given ASCII (or ANSI) code. For instance, the chr(65) returns "A"
• asc (unary operator) retrieves the ASCII (or ANSI) code of the first character in a string. For instance, the asc("A") returns 65
• a startwith b (binary operator) specifies whether a string starts with specified string ( 0 if not found, -1 if found ). For instance "Mihai" startwith "Mi" returns -1
• a endwith b (binary operator) specifies whether a string ends with specified string ( 0 if not found, -1 if found ). For instance "Mihai" endwith "ai" returns -1
• a contains b (binary operator) specifies whether a string contains another specified string ( 0 if not found, -1 if found ). For instance "Mihai" contains "ha" returns -1
• a left b (binary operator) retrieves the left part of the string. For instance "Mihai" left 2 returns "Mi".
• a right b (binary operator) retrieves the right part of the string. For instance "Mihai" right 2 returns "ai"
• a lfind b (binary operator) The a lfind b (binary operator) searches the first occurrence of the string b within string a, and returns -1 if not found, or the position of the result ( zero-index ). For instance "ABCABC" lfind "C" returns 2
• a rfind b (binary operator)  The a rfind b (binary operator) searches the last occurrence of the string b within string a, and returns -1 if not found, or the position of the result ( zero-index ). For instance "ABCABC" rfind "C" returns 5.
• a mid b (binary operator) retrieves the middle part of the string a starting from b ( 1 means first position, and so on ). For instance "Mihai" mid 2 returns "ihai"
• a count b (binary operator) retrieves the number of occurrences of the b in a. For instance "Mihai" count "i" returns 2.
• a replace b with c (double binary operator) replaces in a the b with c, and gets the result. For instance, the "Mihai" replace "i" with "" returns "Mha" string, as it replaces all "i" with nothing.
• a split b (binary operator) splits the a using the separator b, and returns an array. For instance, the weekday(value) array 'Sun Mon Thu Wed Thu Fri Sat' split ' ' gets the weekday as string. This operator can be used with the array.
• a like b (binary operator) compares the string a against the pattern b. The pattern b may contain wild-characters such as *, ?, # or [] and can have multiple patterns separated by space character. In order to have the space, or any other wild-character inside the pattern, it has to be escaped, or in other words it should be preceded by a \ character. For instance value like `F*e` matches all strings that start with F and ends on e, or value like `a* b*` indicates any strings that start with a or b character.
• a lpad b (binary operator) pads the value of a to the left with b padding pattern. For instance, 12 lpad "0000" generates the string "0012".
• a rpad b (binary operator) pads the value of a to the right with b padding pattern. For instance, 12 lpad "____" generates the string "12__".
• a concat b (binary operator) concatenates the a (as string) for b times. For instance, "x" concat 5, generates the string "xxxxx".

The operators for dates are:

• time (unary operator) retrieves the time of the date in string format, as specified in the control's panel. For instance, the time(#1/1/2001 13:00#) returns "1:00:00 PM"
• timeF (unary operator) retrieves the time of the date in string format, as "HH:MM:SS". For instance, the timeF(#1/1/2001 13:00#) returns "13:00:00"
• shortdate (unary operator) formats a date as a date string using the short date format, as specified in the control's panel. For instance, the shortdate(#1/1/2001 13:00#) returns "1/1/2001"
• shortdateF (unary operator) formats a date as a date string using the "MM/DD/YYYY" format. For instance, the shortdateF(#1/1/2001 13:00#) returns "01/01/2001"
• dateF (unary operator) converts the date expression to a string expression in "MM/DD/YYYY HH:MM:SS" format. For instance, the dateF(#01/01/2001 14:00:00#) returns #01/01/2001 14:00:00#
• longdate (unary operator) formats a date as a date string using the long date format, as specified in the control's panel. For instance, the longdate(#1/1/2001 13:00#) returns "Monday, January 01, 2001"
• year (unary operator) retrieves the year of the date (100,...,9999). For instance, the year(#12/31/1971 13:14:15#) returns 1971
• month (unary operator) retrieves the month of the date ( 1, 2,...,12 ). For instance, the month(#12/31/1971 13:14:15#) returns 12.
• day (unary operator) retrieves the day of the date ( 1, 2,...,31 ). For instance, the day(#12/31/1971 13:14:15#) returns 31
• yearday (unary operator) retrieves the number of the day in the year, or the days since January 1st ( 0, 1,...,365 ). For instance, the yearday(#12/31/1971 13:14:15#) returns 365
• weekday (unary operator) retrieves the number of days since Sunday ( 0 - Sunday, 1 - Monday,..., 6 - Saturday ). For instance, the weekday(#12/31/1971 13:14:15#) returns 5.
• week (unary operator) retrieves the week number as a number between 1 and 53. For instance, the week(#12/31/1971 13:14:15#) returns 1.
• hour (unary operator) retrieves the hour of the date ( 0, 1, ..., 23 ). For instance, the hour(#12/31/1971 13:14:15#) returns 13
• min (unary operator) retrieves the minute of the date ( 0, 1, ..., 59 ). For instance, the min(#12/31/1971 13:14:15#) returns 14
• sec (unary operator) retrieves the second of the date ( 0, 1, ..., 59 ). For instance, the sec(#12/31/1971 13:14:15#) returns 15
• value format 'flags' (binary operator) formats a date expression with specified flags. The format method formats numeric (depends on the type of the value, explained at operators for numbers) or date expressions. If not supported, the value is formatted as a number (the date format is supported by newer version only). The flags specifies the format picture string that is used to form the date. Possible values for the format picture string are defined below. For instance, the date(value) format `MMM d, yyyy` returns "Sep 2, 2023"

  The following table defines the format types used to represent days:

  • d, day of the month as digits without leading zeros for single-digit days (8)
  • dd, day of the month as digits with leading zeros for single-digit days (08)
  • ddd, abbreviated day of the week as specified by the current locale ("Mon" in English)
  • dddd, day of the week as specified by the current locale ("Monday" in English)

  The following table defines the format types used to represent months:

  • M, month as digits without leading zeros for single-digit months (4)
  • MM, month as digits with leading zeros for single-digit months (04)
  • MMM, abbreviated month as specified by the current locale ("Nov" in English)
  • MMMM, month as specified by the current locale ("November" for English)

  The following table defines the format types used to represent years:

  • y, year represented only by the last digit (3)
  • yy, year represented only by the last two digits. A leading zero is added for single-digit years (03)
  • yyy, year represented by a full four or five digits, depending on the calendar used. Thai Buddhist and Korean calendars have five-digit years. The "yyyy" pattern shows five digits for these two calendars, and four digits for all other supported calendars. Calendars that have single-digit or two-digit years, such as for the Japanese Emperor era, are represented differently. A single-digit year is represented with a leading zero, for example, "03". A two-digit year is represented with two digits, for example, "13". No additional leading zeros are displayed.
  • yyyy, behaves identically to "yyyy"

  The following table defines the format types used to represent era:

  • g, period/era string formatted as specified by the CAL_SERASTRING value (ignored if there is no associated era or period string)
  • gg, period/era string formatted as specified by the CAL_SERASTRING value (ignored if there is no associated era or period string)

  The following table defines the format types used to represent hours:

  • h, hours with no leading zero for single-digit hours; 12-hour clock
  • hh, hours with leading zero for single-digit hours; 12-hour clock
  • H, hours with no leading zero for single-digit hours; 24-hour clock
  • HH, hours with leading zero for single-digit hours; 24-hour clock

  The following table defines the format types used to represent minutes:

  • m, minutes with no leading zero for single-digit minutes
  • mm, minutes with leading zero for single-digit minutes

  The following table defines the format types used to represent seconds:

  • s, seconds with no leading zero for single-digit seconds
  • ss, seconds with leading zero for single-digit seconds

  The following table defines the format types used to represent time markers:

  • t, one character time marker string, such as A or P
  • tt, multi-character time marker string, such as AM or PM

The supported ternary operators, all these with the same priority 0, are :

• ? ( Immediate If operator ), returns and executes one of two expressions, depending on the evaluation of an expression. The syntax for ? operator is:

  expression ? true_part : false_part

  It evaluates the expression and returns the true_part if the expression is true; otherwise, it returns the false_part. For example, %0 = 1 ? 'One' : (%0 = 2 ? 'Two' : 'not found') returns 'One' if the value is 1, 'Two' if the value is 2, and 'not found' for any other value. The case statement is an n-ary equivalent.

The supported n-ary operators are (with priority 5):

• array (at operator), returns the element from an array by using its index (0-based). It returns an empty value if the element is not found, or the associated element in the collection if it is found. The syntax for the array operator is:

expression array (c1,c2,c3,...cn)

where c1, c2, ..., are constant elements, which could be numeric, date, or string expressions. For instance, the expression month(value)-1 array ('J','F','M','A','M','Jun','J','A','S','O','N','D') is equivalent to the following case expression: month(value)-1 case (default:''; 0:'J';1:'F';2:'M';3:'A';4:'M';5:'Jun';6:'J';7:'A';8:'S';9:'O';10:'N';11:'D')

• in (include operator), specifies whether an element is found in a set of constant elements. The in operator returns -1 (True) if the element is found, and 0 (False) if it is not. The syntax for the in operator is:

expression in (c1,c2,c3,...cn)

where c1, c2, ..., are constant elements, which could be numeric, date, or string expressions. For instance, the expression value in (11,22,33,44,13) is equivalent with (expression = 11) or (expression = 22) or (expression = 33) or (expression = 44) or (expression = 13). The in operator is more efficient than the equivalent or version. When working with a large number of constant elements, the in operator reduces the number of operations. For example, if the collection has 1000 elements, the in operator may take up to 8 operations to find if an element is in the set, while using the or statement could require up to 1000 operations. Therefore, the in operator is generally faster and recommended for large collections.

• switch (switch operator), returns the value found in the collection, or a predefined value (default) if the element is not found. The syntax for the switch operator is:

  expression switch (default,c1,c2,c3,...,cn)

  Where c1, c2, ..., are constant elements, and default is the value returned if the element is not found in the collection. The constant elements can be numeric, date, or string expressions. The equivalent syntax is: "%0 = c 1 ? c 1 : ( %0 = c 2 ? c 2 : ( ... ? . : default) )". The switch operator is very similar to the in operator, except that it returns the value itself instead of -1. The first element in the switch is always returned when the element is found, and the default value is returned if the element is not found. For instance, the %0 switch ('not found',1,4,7,9,11) will return 1, 4, 7, 9 or 11, or 'not found' for any other value. Like the in operator, the switch operator uses binary search to find the fitting element, making it faster than using ? (immediate if operator) alternatives.

• case (case operator) returns and executes one of several expressions based on the evaluation of the expression. It is similar to the ? (immediate IF) operator, but with multiple possible outcomes. The syntax for the case operator is:

  expression case ([default : default_expression ; ] c1 : expression1 ; c2 : expression2 ; c3 : expression3 ;....)

  • If the default part is missing, the case operator returns the value of the expression if it is not found in the collection of cases (c1, c2, ...).
  • If the value of the expression matches one of the constants (c1, c2, ...), the corresponding expression (expression1, expression2, ...) is executed and returned.
  • If the value is not found in any of the cases, the default_expression is executed and returned.

  The constants (default, c1, c2, ...) and expressions (expression1, expression2, ...) can be numbers, dates, or strings.

  For instance, the date(shortdate(value)) case (default:0 ; #1/1/2002#:1 ; #2/1/2002#:1; #4/1/2002#:1; #5/1/2002#:1) indicates that only the dates #1/1/2002#, #2/1/2002#, #4/1/2002#, and #5/1/2002# will return 1, while all other dates will return 0. Another example specifies non-working hours for specific dates: date(shortdate(value)) case(default:0;#4/1/2009# : hour(value) >= 6 and hour(value) <= 12 ; #4/5/2009# : hour(value) >= 7 and hour(value) <= 10 or hour(value) in(15,16,18,22); #5/1/2009# : hour(value) <= 8). This defines the working hours for specific dates as follows::

  • #4/1/2009#, from hours 06:00 AM to 12:00 PM
  • #4/5/2009#, from hours 07:00 AM to 10:00 AM and hours 03:00PM, 04:00PM, 06:00PM and 10:00PM
  • #5/1/2009#, from hours 12:00 AM to 08:00 AM

  This shows how the case operator allows for flexible date and time-based logic, defining specific conditions for various cases and defaults.

The in, switch, and case statements use binary search to look for elements, making them faster than using iif and or expressions. The priority of operations inside the expression is determined by parentheses () and the priority of each operator.