Currency Field - Remove Dollar Sign, Commas, Decimals pad left to 7 characters

[crogers111]

CR XI
SQL

I've removed the dollar sign, decimal and commas:

replace(replace(replace(totext({Data.AmountPaid}),"$",""),",",""),".","")

but can't get this formula to format to 7 characters.

e.g

7.00 should be 0000700
0 should be 0000000
700 should be 0070000
123.57 should be 0012357

I thought the following would do it:

replace(replace(replace(totext({Data.AmountPaid},"0000000"),"$",""),",",""),".","")

it formats to 7 but truncates the decimals

and this doesn't do the trick either:
replace(replace(replace(totext({Data.AmountPaid},2, "0000000"),"$",""),",",""),".","")

What am I missing ?

 

[pmax9999]

Try:

ToText({Data.AmountPaid}*100, '000000#')

Cheers
Pete

 

[crogers111]

That puts me close. The only issue appears to be that values = 0 are displaying with 6 places instead of 7
e.g. 000000

but I should be able to add something like:

if {Data.AmountPaid} = 0 then
"0000000"
else

ToText({Data.AmountPaid}*100, '000000#')

Thanks for the help

 

[pmax9999]

In that case:

ToText({@Data.AmountPaid}*100, '0000000')

 

[crogers111]

ah, of course.

thanks again.