Format a number in a formula

[mart10]

I am using CR 10

How can i get the followwing :

{AccountDetailsIBAN.Currency}&" "&ToNumber({?AboveLimit})-ToNumber({?UptoLimit})

to show GBP 10

rather than GBP 10.0 I ant to format to no d.p.

 

[briangriffin]

When you use the ampersand to join fields that are not the same datatype, the conversion is implicit. In your example the 10.0 value is text.

I usually do explicit type conversion, both because I think it's easier to know what happening at first glance, and because you have more control over the output.

{AccountDetailsIBAN.Currency} & " " & totext(ToNumber({?AboveLimit})-ToNumber({?UptoLimit}),"#")

By using the totext function, you can pass in the format string ("#") to display the number any way you like. Take a look at totext and format strings in CR help for all the options.

 

[infinitizon]

Use {AccountDetailsIBAN.Currency}&" "&Totext(ToNumber({?AboveLimit})-ToNumber({?UptoLimit}),0)

 

[mart10]

Doesnt quite give what I want. i think my formula may have confused you. I want to see output saying for example

GBP 1,000 to 35,0000

The formula implies a subtraction but this is my error. how do i solve this please?

 

[Madawc]

You already got the basic answer from infinitizon. If you want the two numbers, make it

{AccountDetailsIBAN.Currency}
& " " & 
Totext(ToNumber({?AboveLimit},0)
& " - " &
Totext(ToNumber({?UptoLimit}),0)

Look up ToText using Crsytal's Help function, it has many useful options, especially for dates. In this case, the , 0 says 'no decimal places'.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[mart10]

I tried that and it just didnt like the format, kept saying

the ) is mising i couldnt see any reason for that msg and when i tried adding a ) it made the error worse

 

[IanWaterman]

You just have your parenthesis in wrong place.

Copy and paste actual formula here if you have not already found error.

Ian

 

[mart10]

sorry you make think Im not doing this correctly but this is what i allways do. I pasted your formula in and get the ) error

Bizare

 

[IanWaterman]

You pasted your original formula, what about the new formula with suggested changes. I thought this one was failing, your original one worked but just gave you the wrong format.

Ian

 

[mart10]

I pasted in exactly as above at 4:24 (and below)


{AccountDetailsIBAN.Currency}& " " & Totext(ToNumber({?AboveLimit},0)& " - " &Totext(ToNumber({?UptoLimit}),0)

It comes back with the ) error, I just tried again and still does it

 

[lbass]

For every open paren there must be a closed one:

{AccountDetailsIBAN.Currency}& " " &
Totext(ToNumber({?AboveLimit}),0)& " to " &
Totext(ToNumber({?UptoLimit}),0)

-LB

 

[mart10]

Thanks that one worked first time

 

[infinitizon]

The first thing you should confirm is the parameter {?AboveLimit} and {?UptoLimit}. did you already specify them as numbers?
If you did, then all you need is:

{AccountDetailsIBAN.Currency}&" "&Totext({?AboveLimit}-{?UptoLimit},0)

or better still

numberVar AboveLimit := {?AboveLimit} ;
numberVar UptoLimit := {?UptoLimit} ;
{AccountDetailsIBAN.Currency}&" "&Totext((AboveLimit - UptoLimit),0)

The conversion to number you used may only be neccessary if you did not specify the parameter as number already

Hope this works.