Converting Number to text and then back again

[teamssc]

Crystal 8.5 on XP

I'm probably making this harder then it needs to be. I've got a numeric field (conthist.duration) which contains time and sales amounts depending on the conthist.rectype field. Thus "T" = time or "S" = Sale or "S U" = unsuccessful sale. First I'm trying to get a dollar amount to show correctly for a sale to total a day of business. However, if it's not an "S" or "S U" conthist.rectype I don't want it to show anything. If it is an "S U" then I want it to show "$0.00".

My formula works but doesn't allow me to total a day correctly.
Field Name: @Sale

If {ContHist.RECTYPE} = "S U" then "0.00" else
if {ContHist.RECTYPE} = "S" then (CStr(val({ContHist.DURATION})))

I would have liked to avoid the conversion of the numeric value "ContHist.RECTYPE" to a string, (CStr(val({ContHist.DURATION}))), but thats the only way I could get it to work with the first half of the equation.

Ok, that explained I need help with two things.

#1. I would like to show that value as Dollars (curency).
#2. I would like to sum the result but since it's text I can't sum(@Sale). Guess because it is text? So I created a second field SaleConvert and tried variations of the following. CDbl(val({@Sale})) While it does return values they are not conistantly correct. Thus $800 shows correctly as $800.00 but $1000 shows as $1.00 and $1500 also shows as $1.00.

Sorry this is so long but I'm trying to explain my complete issue in one shot. Thanks!

 

[MCuthill]

donedds,

I think I have a solution for you... I built a quick report that evaluated the state of a text field and returned either 0.00 or the value of a field of number type.

Try the following Code in your {@Sale}:

[blue]IF[/blue] {ContHist.RECTYPE} = "S U" [blue]THEN[/blue] 0 [blue]ELSE[/blue]
[blue]IF[/blue] {ContHist.RECTYPE} = "S" [blue]THEN[/blue] {ContHist.DURATION}

This is on the assumption that the DURATION field is of numeric type. This should allow you to do a summation on the {@Sale} field.

Hope this helps.


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[synapsevampire]

I think all you need is:

IF {ContHist.RECTYPE} = "S" THEN
{ContHist.DURATION}
else
0

The point being don't create a text value.

-k

 

[teamssc]

I actually just came up with the following explaintation of all places at the businessobjects.com website.... but I got there via Google...

Anyway below is what they propose (which works). The problem was created because there where Null or blank values in the field....

So you start with a new field called anything you want... in my case "SalesNumber". I was then able to sum @SalesNumber.

What you guys propose might work too. I really appreciate this forum... thanks!

>>>>>>>>>>>>>
//This formula verifies if the field does not contain null or blank values and if the field contains //numeric characters. If yes, the characters are converted to a number value. If no, the number //value 0 is returned.


If Not isNull({@Sale}) Then

If isNumeric({@Sale}) Then ToNumber({@Sale})
>>>>>>>>>>>>>>>>>>>>

The link to the solution is

http://technicalsupport.businessobj...2&sliceId=&dialogID=360384&stateId=1 0 356275