Siebel VB Language Reference > VB Language Reference >

Format Function

This standard VB function returns a formatted string of an expression based on a given format.

Syntax

Format[$](expression[, format])

Select one of the topics that follow for a detailed description of format strings.

Returns

The expression in the specified format.

Usage

The Format function formats the expression as a number, date, time, or string depending upon the format argument. The dollar sign ($) in the function name is optional. If it is included, the return type is string. Otherwise the function returns a variant of vartype 8 (string). As with any string, you must enclose the format argument in quotation marks ("").

Numeric values are formatted as either numbers or date/times. If a numeric expression is supplied and the format argument is omitted or null, the number is converted to a string without any special formatting.

Both numeric values and variants can be formatted as dates. When formatting numeric values as dates, the value is interpreted according the standard Basic date encoding scheme. The base date, December 30, 1899, is represented as zero, and other dates are represented as the number of days from the base date.

Strings are formatted by transferring one character at a time from the input expression to the output string.

CAUTION:  The Format function does not give the correct format if the format string does not match the Regional Settings, or if the Date in the Windows setting is not set to the U.S. format.

Formatting Numbers

The predefined numeric formats with their meanings are as follows:

To create a user-defined numeric format, follow these guidelines:

For a simple numeric format, use one or more digit characters and (optionally) a decimal separator. The two format digit characters provided are zero, "0", and number sign, "#". A zero forces a corresponding digit to appear in the output; while a number sign causes a digit to appear in the output if it is significant (in the middle of the number or non-zero).

A comma placed between digit characters in a format causes a comma to be placed between every three digits to the left of the decimal separator.

NOTE:  Although a comma and period are used in the format specification to denote separators for thousands and decimals, the output string contains the appropriate character based upon the current international settings for your machine.

Numbers can be scaled either by inserting one or more commas before the decimal separator or by including a percent sign in the format specification. Each comma preceding the decimal separator (or after all digits if no decimal separator is supplied) scales (divides) the number by 1000. The commas do not appear in the output string. The percent sign causes the number to be multiplied by 100. The percent sign appears in the output string in the same position as it appears in format.

Characters can be inserted into the output string by being included in the format specification. The following characters are inserted in the output string in a location matching their position in the format specification:

        -  + $ ( space

Any set of characters can be inserted by enclosing them in double quotes. Any single character can be inserted by preceding it with a backslash, \.

You can use the standard VB Chr function if you need to embed quotation marks in a format specification. The character code for a quotation mark is 34.

Numbers can be formatted in scientific notation by including one of the following exponent strings in the format specification:

     E- E +  e- e +

Precede the exponent string with one or more digit characters. The number of digit characters following the exponent string determines the number of exponent digits in the output. Format specifications containing an uppercase E result in an uppercase E in the output. Those containing a lowercase e result in a lowercase e in the output. A minus sign following the E causes negative exponents in the output to be preceded by a minus sign. A plus sign in the format causes a sign to always precede the exponent in the output.

A numeric format can have up to four sections, separated by semicolons. If you use only one section, it applies to every value. If you use two sections, the first section applies to positive values and zeros, the second to negative values. If you use three sections, the first applies to positive values, the second to negative values, and the third to zeros. If you include semicolons with nothing between them, the undefined section is printed using the format of the first section. The fourth section applies to Null values. If it is omitted and the input expression results in a NULL value, Format returns an empty string.

Formatting Dates and Times

As with numeric formats, there are several predefined formats for formatting dates and times:

In a user-defined format for a date, the format specification contains a series of tokens. Each token is replaced in the output string by its appropriate value.

A date can be output by using a combination of the following tokens:

Finer control over the output is available by including format tokens that deal with the individual components of the date-time. These tokens are:

By default, times display using a military (24-hour) format. Several tokens are provided in date time format specifications to change this default. They use a 12-hour format. These are:

Any set of characters can be inserted into the output by enclosing them in double quotes. Any single character can be inserted by preceding it with a backslash, "\".

Formatting Strings

By default, string formatting transfers characters from left to right. The exclamation point, !, when added to the format specification causes characters to be transferred from right to left. By default, characters being transferred are not modified. The less than, <, and the greater than, >, characters force case conversion on the transferred characters. Less than forces output characters to be in lowercase. Greater than forces output characters to be in uppercase.

Character transfer is controlled by the at sign, @, and the ampersand, &, characters in the format specification. These operate as follows:

A format specification for strings can have one or two sections separated by a semicolon. If you use one section, the format applies to all string data. If you use two sections, the first section applies to string data, the second to Null values and zero-length strings.

Examples

This example demonstrates some of the string-formatting tokens.

Sub Button1_Click
   Dim msgtext As String
   msgtext = Format("Section #AB-234", "<\[&&&&&&&&&&&&&&&\]") _
   & Chr$(13) & Chr$(13) & Format("incoming", ">@@@@@@@@\!\!") _
   & Chr$(13) & Chr$(13) _
   & Format("Profits are expected to rise.", _
   "!&&&&&&&&&&&&&&&&&")
End Sub

This example calculates the square root of 2 as a double-precision floating point value and displays it in scientific notation.

Sub Button1_Click
   Dim value As Double
   Dim msgtext As String
   value = CDbl(Sqr(2))
   msgtext = "The square root of 2 is " & Format(value, "Scientific")
End Sub

This example uses several different date-formatting tokens to format the result of the Now function, which returns the current date and time on the computer's clock.

Sub ClickMe_Click
   dim msgtext As String
   msgtext = Now & Chr$(13) & Chr$(13) _
   & "Today is " & Format(Now, "dddd") & ", " _
   & Format(Now, "mmmm") & " " & Format(Now, "dd") & ", " _
   & Format(Now, "yyyy") & "." _
   & Chr$(13) & "The time is " & Format(Now, "h:nn am/pm") _
   & " and " & Format(Now, "s") & " seconds."
End Sub

For other examples of the Format function, read CCur Function, FV Function, and GoTo Statement.

See Also

Asc Function
CCur Function
CDbl Function
Chr Function
CInt Function
CLng Function
CSng Function
CStr Function
CVar Function
CVDate Function
Str Function