Microsoft KB Archive/172849

= The TRANSFORM function does not format a calculated field properly in Visual FoxPro =

Article ID: 172849

Article Last Modified on 1/31/2005

-

APPLIES TO


 * Microsoft FoxPro 2.6a Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q172849



SYMPTOMS
The TRANSFORM function does not properly format a calculated field in the Report Designer. However, this function properly formats a non-calculated field in a report. In FoxPro 2.6 for Windows, the calculated field appears, but the format is incorrect. In Visual FoxPro for Windows versions 3.x and 5.x, the calculated field appears as asterisks.



CAUSE
The TRANSFORM function returns a character value. Because numeric functions are applied to a calculated field, changing the field to a character value causes erroneous results.



RESOLUTION
In FoxPro 2.6 for Windows, create a report variable to perform the desired calculations and then format the report variable.

In Visual FoxPro for Windows versions 3.x and 5.x, just Sum the field and check the Currency check box in the Format dialog of the field (it appears under the Numeric category.) In the Format text box in the Report Expression dialog of the calculated field, use the following to format the output:

999,999.99

Of course, a report variable could also be used in Visual FoxPro.



STATUS
This behavior is by design.



Steps to Reproduce Behavior
Perform the following steps in FoxPro 2.6 for Windows:  Open the Detail table located in the \tutorial directory. Set the order to the Ino tag. Create a new report and add the Price field to the Detail line. Add a Data Grouping to the report based on the Ino field. Add the Ino field to the Group Header and add a second field referencing Price in the Group Footer band.</li> In the Price field in the Group Footer, click the Calculate check box, choose Sum and Reset on Detail.ino.</li>  In the Price field in the Detail and Group Footer bands, add the following code to the Expression box: TRANSFORM(Price,"$$$$,$$$.99") </li> Add a Report Variable to the report, xtotal, and set the Value to Store to Detail.price, Reset to Detail.ino, and Calculate to Sum.</li> Add a new field to the Group Footer and use the Report Variable, xtotal, as the expression. Format as Currency if desired.</li> Preview the report and notice that the calculated field is not preceded with a dollar sign and it carries the number out to five decimal places. However, the report variable field has the correct format.</li></ol>

Perform the following steps in Visual FoxPro versions 3.0b, 5.0x or 6.0: <ol> Open the Orders table located in the \Samples\Data folder.</li> Create a report and add the orders table to the data environment.</li> Set the order of the table to the Cust_id field using the Order property of the cursor in the data environment.</li> Add a data grouping to the report based on the Cust_id field.</li>  Place the Cust_id field in the Group Header band and place the order_amt field in the Detail band. Double-click the field to bring up the Report Expression dialog box and in the Expression box, add the following code: TRANSFORM(order_amt,"$$$,$$$,$$$.99") Click OK to close the Report Expression dialog box. </li> Copy this field into the Group Footer band. Double-click the field, choose the Calculations button, and select the button for Sum. Click OK to close the Report Expression dialog box.</li> Preview the report. Note the non-calculated fields appear formatted correctly. However, the calculated fields appear as a row of asterisks.</li></ol>

For additional information, please see the following article in the Microsoft Knowledge Base:

102704 FIX: Can't Give Numeric the Currency Format in Report Writer

Additional query words: FoxWin VFoxwin

Keywords: kbprb KB172849

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.