How to format a number like ($999,999.99)

[mikeopolo]

Hi all; this question could only come from an accountant/excel user...(and VFP novice)

Is there a way to format a number in report layout to the above, *and* have all the positive and negative numbers line up, like the excel format [($999,999.99_);($999,999.99)] (the '_)' shifts the positive numbers over to the left by the amount of space taken up by the ')'.

I've specified Blank when zero, parentheses and currency in my format properties. This puts the '(' over to the left of the field, which leaves a space between that and the '$', and the whole string is right aligned, which means the actual numbers don't line up vertically. Searches in help and forums have drawn a blank.

Any suggestions? (Mis)using VFP9 <g>.


Regards
Mike

 

[alvechurchdata]

I've specified Blank when zero, parentheses and currency in my format properties

Sounds like you've tried everything I would suggest. You might have to convert the value to a string of characters and write code to pad it out to the right length.

Could you post an example of the format you want? I've just tried and failed to apply your format code in Excel - I'm not a heavy user of Excel and never use accounting formats.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Mike,

Convert the number to a string (perhaps using TRANSFORM() with a picture in the second param), then use PADL() to line up the result within the string.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[chpicker]

The Excel format specification actually has 2 sections to it: one for the postitive numbers and one for the negative numbers. There really isn't any way to do that automatically in VFP. However, you can pretend to do so by creating 2 separate fields for the same column. The first would be formatted as "$999,999.99" and visible only when value>=0, the second as "($999,999.99)" and visible only when value<0. Stack one on top of the other and align them as necessary.

It's not perfect, but it should work.

Ian

 

[mikeopolo]

Hi Ian, thanks for your interesting suggestion. I've used the same idea for other reasons, it should work well for this case.

Mike