TRANSFORM( ) Function
Returns a character string from an expression in a format determined by a format code.
Note Using TRANSFORM( ) with a large negative number that contains a decimal might result in numeric overflow when that number is passed in as a memory variable. You should use the STR( ) function instead with such numbers.
Specifies the character, currency, date, object, or numeric expression to format.
Specifies one or more format codes that determine how the expression is formatted. The following table lists the available format codes.
Format codes Description @B Left-justifies numeric data within the display region. @C Appends CR to positive currency or numeric values to indicate a credit. @D Converts Date and DateTime values to the current SET DATE format. @E Converts Date and DateTime values to a BRITISH date format. @L Pads numeric data with leading zeroes. @R The transformation uses a format mask. The mask characters are not stored to the transformed value. Use only with character or numeric data. Mask characters include:
9 or # Represents a character or number.
! Converts lower-case letters to uppercase letters.
Note Visual FoxPro does not display specified mask characters that exceed the converted string length of the passed expression.
@T Trims leading and trailing spaces from character values. @X Appends DB to negative currency or numeric values to indicate a debit. @Z If 0, converts currency or numeric values to spaces. @( Encloses negative currency or numeric values in parentheses. @^ Converts currency or numeric values to scientific notation. @0 Converts numeric or currency values to their hexadecimal equivalents. The numeric or currency value must be positive and less than 4,294,967,296. @! Converts an entire character string to uppercase. @$ Adds the current currency symbol specified by SET CURRENCY to currency and numeric values. By default, the symbol is placed immediately before or after the value. However, the currency symbol and its placement (specified with SET CURRENCY), the separator character (specified with SET SEPARATOR) and the decimal character (specified with SET POINT) can all be changed. X Specifies the width of character values. For example, if cFormatCodes is 'XX', 2 characters are returned. Y Converts logical True (.T.) and False (.F.) values to Y and N, respectively. ! Converts a lowercase character to uppercase in the corresponding position in a character string. . Specifies the decimal point position in currency and numeric values. , Separates digits to the left of the decimal point in currency and numeric values. 9 Specifies width of numeric values.
For example, if cFormatCodes is '999.99', the numeric characters are formatted with three characters to the left of the decimal point, and two characters to the right.
Note When specifying cFormatCodes for numeric values, the format code must be at least the size of the value you want to display.
The following example shows the results when the format code is missing, less than, and equal to the size of the value you want to display.
CREATE CURSOR myCursor (col1 n(5,2)) INSERT INTO myCursor VALUES (-555.5) * No format code and returns **.** ? TRANSFORM(myCursor.col1 ) * A format code 1 less than value returns ***.** ? TRANSFORM(myCursor.col1,'999.99' ) * A format code same size as value returns -556.00 ? TRANSFORM(myCursor.col1,'9999.99' )
When the expression value is longer than the width of the numeric field, Visual FoxPro forces the value to fit by performing the following steps:
- Truncate decimal places and round the remaining decimal portion of the field.
- If the value does not fit, store the field contents using scientific notation.
- If the value still does not fit, replace the field contents with asterisks.
If you omit cFormatCodes, Visual FoxPro performs a default transformation on eExpression. The following table describes the transformation performed for each data type eExpression can assume.
Data type Transformation description Character Performs no transformation. Currency Transformation is determined by the settings specified in the Regional tab of the Options dialog box. Date Performs a DTOC( ) transformation on the date. DateTime Performs a TTOC( ) transformation on the date time. General Returns "Gen" if the general field contains an object or "gen" if the general field does not contain an object. Logical Transforms Logical True (.T.) and False (.F.) values to the character strings ".T." and ".F." respectively. Memo Performs no transformation. Numeric (includes Double, Float, or Integer data types) Removes trailing zeros from the decimal portion of a numeric value.
If the numeric value is a whole number, a decimal point is not included in the transformed value, for example, 4.0 is transformed to 4.
If the numeric value is less than one but greater than negative one, zero is included before the decimal point, for example, .4 is transformed to 0.4.
Object Returns the character string "(Object)".
Character data type. TRANSFORM( ) returns character string from an expression in a format determined by a format code.
STORE 12.34 TO gnPrice CLEAR ? TRANSFORM(gnPrice, '$$$$.99') && Displays $12.34 ? TRANSFORM(_SCREEN) && Displays (Object)