Cannot transfer formula from Access to Crystal

[intern1]

Hi I am new to Crystal and am having trouble getting a formula that I have used in Access to work properly in Crystal. I recognize that it will be written differently but have not been able to properly duplicate it. Thank you for the asssistance.

Here is the formula...

=IIf(Sum(nz(Abs([Market Value]),0))>0,Sum(NZ([OrigPrice],0)*NZ(Abs([Market Value]),0))/Sum(nz(Abs([Market Value]),0)),IIf(Sum(nz(Abs([Market Value]),0))<0,-Sum(nz([OrigPrice],0)*nz(Abs([Market Value]),0))/Sum(nz(Abs([Market Value]),0)),0))

 

[Madawc]

First, use Crystal's totalling system to get the totals for Market Value etc. Maybe put them in their own formula fields for easy use elsewhere. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Then you need something like

if @SumNZMarketValue = 0 then 0
else if @SumMarketValue > 0 
     then (@SumOrigPrice * @SumNZMarketValue) / @SumnzMarketValue
     else ....

It's not clear what you mean by NZ and nz. But I suggest you work by stages, showing what you've got at each stage.

Note that a divide by zero will stop the run. You must test for these.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[CoSpringsGuy]

nz is a function to tell the formula to return a default value if null... in this case 0

so since the suggestion is to use Crystals automated totals, that should not be a factor.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

You could set up the report to convert nulls to default values by going into file->report options->convert nulls to default values, and then you should be able to eliminate the nz function.

Not sure how you can get a negative value when using the abs() function.

I avoid using iif in CR because it doesn't handle nulls well--I'm not sure how well it works if you convert nulls. An iif is just a fancy if/then/else, so you could convert it accordingly.

-LB