INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Evaluate number of characters in formula to suppress decimals when needed

Evaluate number of characters in formula to suppress decimals when needed

(OP)
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,"")

RE: Evaluate number of characters in formula to suppress decimals when needed

replace 0.00 with 2

RE: Evaluate number of characters in formula to suppress decimals when needed

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

RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
Awesome ty both!!!

RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
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

RE: Evaluate number of characters in formula to suppress decimals when needed

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

RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
The number I currently see when I widen the field is 2121205.49

RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
I kept working and this worked. Thanks!

If {@Sales_Total} > 999999 then 0 else 2

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close