How to Conditionally Format a Numeric Field

 Last reviewed: June 27, 1995
Article ID: Q104254
The information in this article applies to:
  • Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
  • Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, 2.6

SUMMARY

You can create a field in the Expression Builder of a screen or a report to conditionally alter the formatting of a numeric field or variable. See below for more information.

MORE INFORMATION

To conditionally alter the formatting of a field, you need to combine the IIF() function with the TRANSFORM() function. For example, suppose you have a table with the following field and records: 

   YTDPURCH

   10000
   100
   500
   125000
   500000
   1200
   1200000
   50
   9000
In your report, you want to display the values less than 1000 as plain numbers, the values between 1000 and 100000 as currency with commas, and the values greater than 1000000 as decimal fractions of a million with an "M".

To do this, enter the following expression in the Expression Builder of a single field in the Detail band: 

   IIF(ytdpurch<1000,ytdpurch,IIF(ytdpurch<100000,TRANSFORM(ytdpurch,
   '$$$,$$$.99'),TRANSFORM(ytdpurch/1000000,'99.9')+'M'))
The results will appear as follows: 

     Ytdpurch
   $10,000.00
       100.00
       500.00
         0.1M
         0.5M
    $1,200.00
         1.2M
        50.00
    $9,000.00

Additional reference words: FoxDos FoxWin 2.00 2.50 2.50a writer
multiformatted
multiple formats 2.50b 2.60
KBCategory: kbprg
KBSubcategory: FxtoolGeneral

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 27, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.