Evaluate number of characters in formula to suppress decimals when needed 2

[cpjeffm]

I have a formula called Sales_Total that, at times, can exceed 11 characters in length but the cell width only allows for 11 characters. When this happens, the report returns ######## instead of the amount. What I'd like to do is evaluate the number of characters in the total and if the character length is greater than or equal to 12, display the number without the decimals. So if my total is 123456789.00, which currently returns ########, I want to suppress the .00 so it will show 123456789. If the number is less than or equal to 11, I want it to include the decimals. So if the total is 12345.00, I want it to show 12345.00.

I tried the following in a formula called Sales_Total (LEN) but it's always suppressing the decimals, regardless of the length, and I can't figure out why.

if len(totext({@Sales_Total})) >= 12 then totext({@Sales_Total},0,"") else
if len(totext({@Sales_Total})) < 12 then totext({@Sales_Total},0.00,"")

 

[Charliy]

replace 0.00 with 2

 

[IanWaterman]

This should work but a neater solution is to use number formatting.

if len(totext({@Sales_Total}, 2,"")) >= 12 then totext({@Sales_Total},0,"") else
if len(totext({@Sales_Total}), 2, "") < 12 then totext({@Sales_Total},2,"")

Right click your formula in design mode, select Customise.
Click formula box next to decimal place

If @Sales_Total >= 100000000 then 0 else 2

Ian

 

[cpjeffm]

Awesome ty both!!!

 

[cpjeffm]

Hey Ian. I should've tested your number formatting formula first. Having some issues with it. I like this solution better as I don't have to create another formula.

I've tried the following and I'm still getting #########...

If {@Sales_Total} >= 100000000 then 0 else 2

I also tried...

If {@Sales_Total} > 9999999.99 then 0 else 2

 

[IanWaterman]

It worked fine when I tried it with a big number.

Widen your details section and move the field down and then lengthen field so that ##### disappears.
What do you see?
If decimals have gone then you may have to reduce font size.

Ian

 

[cpjeffm]

The number I currently see when I widen the field is 2121205.49

 

[cpjeffm]

I kept working and this worked. Thanks!

If {@Sales_Total} > 999999 then 0 else 2