FieldFormat Property

Object List Next Object

Defined By: Dynaset

Description:

Allows you to specify a format string that determines how to format the field that is in position FieldIndex in the FieldList property. The value can be a number, a string, a date, or time. The FieldFormat property is only valid when the DatabaseType is set to ODBC.

Details:

Use this property to specify a string that determines the value's format when you are using column values as character strings, or using the data conversion functions to convert values to character strings. You can also use this property to specify a format for the character string in data conversion functions where you are converting character strings to numeric values.

There are two types of format strings you can use:

images/Phx90000.gif Numeric format strings

images/Phx90000.gif Date-Time format strings

The following table shows a few of the many formats that are possible:

Format String Value Formatted value

$#,##0.00 720.5 $720.50

$#,##0.00;($#,##0.00) 725.50265 $725.50

mmm/dd/yy Feb 17, 1995 02/17/95

dd-MMM-yy Feb 8, 1995 08-FEB-95

The symbols listed in a particular string determine the format for the designated value.

Note: You may encounter a symbol that refers to strings that are specified in the Control Panel's International section. To make changes to these strings, you must run the Control Panel program that is standard on Windows or OS/2 systems. When the Control Panel is displayed, click the International icon to view the strings and make any desired changes.

Numeric Format strings

Common formatting symbols for numeric values include decimal points, percent signs, dollar signs, thousand separators (commas), and so on. Numeric format strings can have one or two sections, which allows positive and negative numbers to be formatted differently.

Positive and negative values use the same format in a one-section format string. Phoenix automatically includes a negative sign for any negative numbers. In a two-section format string, the sections are separated by a semicolon. The positive numbers go in the first section, and negative numbers in the second as shown in the following example:

One-Section String Value Formatted Value

0.00 300.1 300.10

-150.5 -150.50

Two-Section String Value Formatted Value

0.00;(0.00) 300.5 300.5

-150.1 (150.10)

The following table lists and describes the valid symbols for numeric format strings:

Use this... To output this...

$ The currency string, which is specified in the Control Panel's International section.

. The decimal point character, which is specified in the Control Panel's International section.

, The thousand’s separator character, which is specified in the Control Panel's International section.

# A digit, if one is supplied. Nothing is output if there is no digit in the designated position.

Symbol Format String Value Formatted String

# ##.## 0 .

56.7 56.7

12.55123 12.55

2000 2000.

0 00.00 0 00.00

56.7 56.70

12.55123 12.55

2000 2000.00

? ???.?? 0 " . "

56.7 " 56.7 "

124.55123 "124.55"

2000 "2000. "

(Quotes included only to illustrate spaces .)

Note: If there are fewer symbols in the format string than there are digits to the left of the decimal, Phoenix automatically extends the the format string to the left (i.e. 2000 formats as 2000.). The value is rounded into the last digit, however, if the value has more digits to the right of the decimal point than there are in the format string (i.e. 12.55123 formats as 12.55).

0 A digit if one is supplied, or a zero if there is no digit in the designated position.

Note: Refer to the “#” symbol Note for additional details.

? A digit if one is supplied, or a space character if there is no digit in the designated position.

Note: Refer to the “#” symbol note for additional details.

% The value as a percent. The value is changed to a percent by multiplying by 100, then the percent character (%) is output. For example, if the format string is “#0%”, the formatted output for 0.25 is “25%”.

e+ e- E+ E- Using scientific notation. Use e- to always include the exponent's sign in the output. Use e+ to output the exponent's sign only if it is negative. For example, if the format string is “0.00e+#0”, 12300 is formatted as “1.23e04” and .004 is formatted as “4.00e-03”. If the format string is “0.00E-#0”, 12300 is formatted as “1.23E+04”, and the formatted output for value .004 is “4.0E-03”.

Note: The case used for the “e” in the format string is the case used in the formatted value.

-+() space Blank spaces, plus or minus signs, or parentheses. These characters are typically used to differentiate positive and negative values and are the only characters that can be included in numeric format strings for direct output. Blank spaces appear as designated.

Note: Enclose other characters or strings that you would like to output directly in quotation marks, or use the backslash (\) symbol.

\ The character immediately following the backslash. For example, if the format string is “0.00 \l\b\s”, the formatted output for value 2.55 is “2.55 lbs”.

“String” 'string' The string (quotation marks are not included in the output). For example, if double quotes are used in the format string “0.00 “lbs””, the formatted output for value 2.55 is “2.55 lbs”.

The same is true for single quotes. For example, if the format string is “0.00 ‘lbs’”, the formatted output for value 2.55 is “2.55 lbs”.

GN (Default) General format for numbers. If the format string is “GN”, the value is formatted as it is, for example, 23.4 is formatted as “23.4”, 123.33475 is formatted as “123.33475”, 0 is formatted as “0”, and so on

Note: To use other symbols with GN, enclose them in brackets [].

GF General fixed format for numbers, which is the “Number Format” in the Control Panel's International section.

Note: To use other symbols with GF, enclose them in brackets [].

GC General currency format for numbers, which is the "Currency Format" in the Control Panel's International section.

Note: To use other symbols with GC, enclose them in brackets [].

[S/n] [S*n] A scaled number. To divide a number by 'n' before it is formatted, use “[S/n]”. To multiply a number by 'n' before it is formatted, use “[S*n]“. The value for ‘n’ must be a power of 10. For example, if the format string is “#0.00[S/1000]”, the formatted output for the value 23450 is “23.45”.

[US] The United States defaults rather than the information specified in the Control Panel's International section. The United States defaults that are substituted include: $ for the currency symbol, periods for decimal points, and commas for thousand separators.

Date-Time Format Strings

Date-time formats allow you to designate the parts of the date or time to output, specify the order of these parts, and indicate whether the month and days are to be spelled out or abbreviated.

The following table describes the valid symbols for the date-time format string:

Use this... To output this...

m mm The month as a number (1-12). To exclude a leading 0 for numbers less than 10, use “m”. To include the 0, use “mm”.

mmm The month as a three-letter abbreviation. The abbreviation is output with the same case as the m's. For example:

mmm feb
Mmm Feb
MMM FEB

mmmm The month spelled out. The name is output with the same case as the m's. For example:

mmm february
Mmmm February
MMMM FEBRUARY

d dd The day of the month as a number (1-31). To exclude a leading 0 for numbers less than 10, use “d”. To include the 0, use “dd”.

ddd The day of the week as a three-letter abbreviation. The abbreviation is output with the same case as the d's. For example:

ddd sat

Ddd Sat

DDD SAT

dddd The day of the week spelled out. The name is output with the same case as the d's:

dddd saturday
Dddd Saturday
DDDD SATURDAY

yy yyyy The number of the year. To output only the last two digits, use “yy.” To output the full four-digit year, use “yyyy.”

h hh The hour of the day (0-23). To exclude a leading 0 for numbers less than 10, use “h” . To include the 0, use “hh”.

Note: The AM/PM symbol determines whether a 12-hour or 24-hour clock is used.

m mm I ii The minute of the hour (0-59). You can use “m” or “i” for minute. If you use “m,” make sure the previous date-time component is an hour symbol or it will be difficult to distinguish it from the month symbol, which is also "m". For numbers less than 10, neither “m” nor “i” output the leading zero, and both "mm” and “ii” do output the leading 0.

ss.ssssss The second of the hour (0-59). You can use one or two “s” symbols to the left of the decimal point and up to six to the right of it. Use the "ss" symbol before the decimal to include the leading zero for seconds less than 10. The optional decimal point and “s” symbols to the right of the decimal are useful for outputting fractional of seconds.

am/pm a/p The “am” or “pm” string. These strings are specified in the International section of the Control Panel. The string is output with the same case as the symbol, for example:

String Output

am/pm “am” or “pm”

AM/PM “AM” or “PM”

To output only the first letter of the string, use the symbol "a/p". If you use a/p, “a” or “p” is output. Likewise, with A/P, “A” or “P” is output.

Note: A 12-hour clock is assumed if this symbol is used. With a 12-hour clock, the output hour numbers are between 1 and 12.

J The Julian value for the date-time. The Julian value date is the number of days since 4712 BC. The Julian time is given as a fraction of a day.

/-.:, space A particular character. Use these characters to separate the parts of a date or time.

Note: These are the only characters you can include in date format strings for direct output. You must enclose all other characters or strings in quotation marks or use the “\” symbol in order to output them directly.

\ The character immediately following the backslash. For example, if the format string is “hh:mm:ss \E\S\T”, the formatted output for value 11:00:10 AM is “11:00:10 EST”.

“string” 'string' The string (quotation marks are not included in the output). For example, if double quotes are used in the format string “hh:mm:ss “EST””, the formatted output of value 11:00:10 AM is “11:00:10 EST”.

The same is true for single quotes. For example, if the format string is “hh:mm:ss ‘EST’”, the formatted output of value 11:00:10 AM is “11:00:10 EST”.

GD (Default) General date format, which is the “Short Date Format” as set in the Control Panel's International section.

Note: To use other symbols with GD, enclose them in brackets [].

GDT General format for dates with times, which is the “TimeFormat” in the Control Panel's International section appended to the “Short Date Format” also in the Control Panel.

Note: To use other symbols with GDT, enclose them in brackets [].

GL General long format for dates, which is the “Long Date Format” set in the Control Panel's International section.

Note: To use other symbols with GL, enclose them in brackets [].

GLT General long format for dates with times, which is the “Time Format” in the Control Panel's International section appended to the “Long Date Format.”

Note: To use other symbols with GLT, enclose them in brackets [].

GT General time formae, which is the “TimeFormat” in the Control Panel's International section.

Note: You cannot combine any other formatting symbols with GT.

[US] The United States defaults instead of the information specified in the Control Panel's International section. This symbol is only valid with GD, GDT, GL, or GLT. For example, if the format string is “GD[US]”, the formatted output of June 21, 1995, is “06/21/95”.

Data Type: String (Read Write)

See Also:

DatabaseType property