# Thread: Format Decimal field, hide decimal when 0

1. Registered User
Join Date
Jun 2005
Posts
79

## Unanswered: Format Decimal field, hide decimal when 0

Hi,

I have a decimal field with 4 decimal places. How do I format the field so that it suppresses the decimal places that are 0 and not display the dot if all 4 decimal places are 0. Basically trim the 0's from the right.

Examples:

123.1200 = 123.12
422.1000 = 422.1
531.0000 = 531
665.9876 = 665.9875

Thanks

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
There is abutton called "Remove Decimal" on the formatting toolbar.
Looks like "0.00" with an arrow.

3. Registered User
Join Date
Jun 2005
Posts
79
I only want to remove the decimal when it is zero. What you suggested just changes the Decimal Places value under Number Format. I'm pretty sure that the only way to achieve this is using a formula, but I don't know how to do it.

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Try this
Code:
```If Right(ToText({Customers.Total Sales}), 3) = ".00" Then
ToText({Customers.Total Sales}, 0)
Else
ToText({Customers.Total Sales}, 2)```
It's not the best way of doing this at alll, but right now I can't think of anything better.

Convert to string - test to see if the 3 rightmost characters ar ".00" - if they are, convert result to text using 0 decimal places, otherwise use 2.

5. Registered User
Join Date
Jun 2005
Posts
79
ok but that still doesn't do what I'm looking for. Please look at the examples I gave:

123.1200 = 123.12
422.1000 = 422.1
531.0000 = 531
665.9876 = 665.9875

6. Registered User
Join Date
Jun 2005
Posts
79
I managed to figure out the formula.

NumberVar decimals;
decimals := ToNumber(StrReverse(Split(ToText(CurrentFieldValue , 4), ".")[2]));

if decimals = 0 then
ToText(CurrentFieldValue, 0)
else
ToText(CurrentFieldValue, Length(ToText(decimals, 0, "")))

• I convert the value to text with 4 decimal places, ToText() seems to automatically use 2 decimal places if you don't specify.
• I split the text at the decimal and get the decimal value which is stored at index 2
• The decimal string is reversed and converted to a number which removes any 0's that were at the front because of the reverse
• The if checks if the number is 0 which means that the decimal string was "0000", else I get the length of the decimal string which I use when formatting the value to text

7. Registered User
Join Date
Sep 2011
Posts
1

just try the code below at format filed->Number->Customize-> Decimals Code Part:

Code:
```if CurrentFieldValue=Int(CurrentFieldValue)  then
0
else
2```
Note: The number '2' is decimal place of yours and you can replace it by your own.

Maxitem Software Corp.

8. Registered User
Join Date
Feb 2012
Posts
1

## thanks holythirteen

holythirteen's solution works awesome !!!!! Thanks man

9. Registered User
Join Date
Apr 2012
Posts
1

## Clumsy but works

In order to set the decimal places to show the significant places only

The logic:
Example: Suppose Price is 0.1023000
strreverse(totext({Price},7)) ==> 0003201.0
tonumber( 0003201.0 ) ==> 3201.0
Round(tonumber( 3201.0 )) ==> 3201
len(totext( 3201 ,0)) ==> 4 , so set decimal places to 4

The code:
If isnull({Price}) then 2
//else len(totext(Round(tonumber(strreverse(totext({Price },7,'')))),0))

10. Registered User
Join Date
Apr 2012
Posts
1

## Chain of ifs to deal with decimal places

I cannot say this is a smart solution to the problem from my point of view, seems sillyk that Crystal forces you to implement such a nasty formula for something a format function should handle easily

This is the solution I am giving for a scenario where, at least, we always want to show the figure with a minimum of 2 decimal places and a max of 7. Put this in the Crystal report field - format - decimals formula

If {Price}=truncate({price},2) then 2 else
If {Price}=truncate({price},3) then 3 else
If {Price}=truncate({price},4) then 4 else
If {Price}=truncate({price},5) then 5 else
If {Price}=truncate({price},6) then 6 else 7

Some examples

3 will be shown as 3.00
3.1 will be shown as 3.10
3.11 will be shown as 3.11
3.111 will be shonw as 3.111
and so on.

if removing completely the decimal part of the figure (for example, price = 6) is required, then add the extra if
If {Price}=truncate({price},0) then 0 else

On which case 3 would be shown as 3

and if you want to show one decimal place too add the following

If {Price}=truncate({price},1) then 1 else

On which case 3.1 would be shown as 3.1

I would be very glad to hear there is a more simple solution for this problem!

11. Registered User
Join Date
Apr 2013
Posts
1

## A Generic Solution

I managed to get a generic rounding formula to truncate trailing zero's.

One thing you have to do is set the rounding and decimals to 1 in the GUI (e.g. not 1.0, 1.00, and so on) and then implement the formula below against the rounding. I think that's because the final rounding number is the greater of what's configured in the GUI and what the formula result is.

Code:
```// If you don't specify the number of decimals, the number will be
// converted to your default number of decimal places which was 2
// in my case, so not ideal when the number to convert may have
// 3 or more dp
stringVar number := ToText({the_table.the_numeric_field}, 8);

// Strip trailing zero's
while (right(number, 1) = "0") do
number := left(number, len(number) - 1);

// Now work out how many decimal places are left and that is the
// rounding amount
len(number) - InStr(number, ".");```
Last edited by JonnyRobot; 04-04-13 at 02:13.

12. Registered User
Join Date
Apr 2013
Posts
1

## Another Suggestion

if (CurrentFieldValue > 1 OR CurrentFieldValue < -1 )then
0
else
2

13. Registered User
Join Date
Jun 2013
Posts
1
I know this is late, but I tried the above solutions, and none fit the request, nor what I needed. In the end, I came up with this to be entered under a custom decimal style. (Format Object->Customize->Decimals Formula)

LEN(Replace(Right(ToText(CurrentFieldValue), Len(ToText(CurrentFieldValue)) - InStr(ToText(CurrentFieldValue), ".")), "0", ""))

Seperate the text after the decimal, replace any 0s with "", and use the remaining length for the final number of decimals. Should function with any number of decimals.
Last edited by Amaethon; 06-19-13 at 11:12.

14. Registered User
Join Date
Nov 2013
Posts
1

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•