To increase compatibility with code written in Visual Basic and Microsoft Access (VBA), C1Report exposes two functions that are not available in VBScript: Iif and Format.
Iif evaluates a Boolean expression and returns one of two values depending on the result. For example:
Iif(SalesAmount > 1000, "Yes", "No")
Format converts a value into a string formatted according to instructions contained in a format expression. The value may be a number, Boolean, date, or string. The format is a string built using syntax similar to the format string used in Visual Basic or VBA.
The following table describes the syntax used for the format string:
| Value Type | Format String | Description |
|---|---|---|
| Number | Percent, % | Formats a number as a percentage, with zero or two decimal places. For example: Format(0.33, "Percent") = "33%" |
| #,###.##0 | Formats a number using a mask. The following symbols are recognized: # digit placeholder 0 digit placeholder, force display , use thousand separators ( enclose negative values in parenthesis % format as percentage For example: Format(1234.1234, "#,###.##") = "1,234.12" |
|
| Currency | Currency, $ | Formats a number as a currency value. Displays number with thousand separator, if appropriate; displays two digits to the right of the decimal separator. For example: Format(1234, "$") = "$1,234.00" |
| Boolean | Yes/No | Returns "Yes" or "No". |
| Date | Long Date | Format(#12/5/1#, "long date") = "December 5, 2001" |
| Short Date | Format(#12/5/1#, "short date") = "12/5/2001" |
|
| Medium Date | Format(#12/5/1#, "medium date") = "05-Dec-01" |
|
| q,m,d,w,yyyy | Returns a date part (quarter, month, day of the month, week of the year, year). For example: Format(#12/5/1#, "q") = "4" |
|
| String | @@-@@/@@ | Formats a string using a mask. The "@" character is a placeholder for a single character (or for the whole value string if there is a single "@"). Other characters are intercodeted as literals. For example: Format("AC55512", "@@-@@/@@") = "AC-555/12" |
| @;Missing | Uses the format string on the left of the semi-colon if the value is not null or an empty string, otherwise returns the part on the right of the semi-colon. For example: Format("@;Missing", "UK") = "UK" |
Note that VBScript has its own built-in formatting functions (FormatNumber, FormatCurrency, FormatPercent, FormatDateTime, and so on). You may use them instead of the VBA-style Format function described here.