Crystal v 8.5 using the 'Replace' function in a formula

dawnyanne
dawnyanne used Ask the Experts™
on
Hi
can anyone help - I am trying to use the 'replace' function in Crystal v8.5. The problem I am having is that I am using fields from a database.
This is my code

replace(totext({acstat.DESCRIPTION}), totext({acstat.QUANTITY}),"ME")

It works fine if I use another string field instead of acstat.Quantiy  but I need to use the Quantity field which is numeric. I thought that by using ToText in front it would be ok. Does anyone know a way aroung this.
Basically I have some text which has a number in it that is not formatted - so I want it to look and see if this number is in another field and then if it is - replace the number in the text with the format.

Many thanks for any help!
Dawnyanne
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use cstr() to convert.
Top Expert 2009

Commented:
Can you post an example of the text, the number and the expected result?

Author

Commented:
Hi

thank you for coming back to me - but it still comes up the same I cant see why it doesnt like it
These are my columns - the Amount Fields are numeric and the Description field is a string. If there is a figure in the amount column then I want the number part i.e. 1000 to be formatted to 1,000.00

Amount           Description
1,000.00          Bought 1000 British Airways Plc Ord 25p

thank you so much for your help
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
The expected result would be

Amount         Description
1,000.00       Bought 1,000.00 British Airways Plc Ord 25p

Its just the formatting with the commas etc

Thank you!!!
Commented:
1. totext(2000) =========> gives 2,000.00  ,so if your description folows with same format then it replaces 2,000.00 as "me" ...that is fine for your example

2. if your description follows just number 2,000  =======> then use totext({quantity},0)

3.  if your description follows just number 2000  =======> then use totext({quantity},0,"")

Commented:
the difference between 2nd 3 rd point is  == comma in the number
Top Expert 2009
Commented:
The replace is therefore something like this.

(replace command.description with your description field and command.amount with your value field. )

The order of the tests is important, and you may find you need to add spaces to the end of the totext(amount.... in the test part...

(i.e. if (" "+totext(amount...)+" " in description..... )
if( totext({Command.amount},2) in {Command.description})then
   {Command.description}
else if (totext({Command.amount},0) in {Command.description})then
   replace({Command.description},totext({Command.amount},0),totext({Command.amount},2))
  else if (totext({Command.amount},2,"") in {Command.description})then
       replace({Command.description},totext({Command.amount},2,""),totext({Command.amount},2))
      else if (totext({Command.amount},0,"") in {Command.description})then
               replace({Command.description},totext({Command.amount},0,""),totext({Command.amount},2))
          else
                {Command.description}

Open in new window

Author

Commented:
THANK YOU THANK YOU THANK YOU!!! So, so much
Very very much appreciated!

I cant thank you enough! You have really helped me out!

Dawnyanne

Commented:
great..thanks for your comment

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial