If Then Formula is not working properly

[Kathyrlcrs]

I am working with 9.0 with an access database.

I have created this formula,

If Not IsNull({Brokerage_Offer.ReducedAmount}) and ({Brokerage_Offer.ReducedAmount} > 0) then
{Brokerage_Offer.ExpireDate} else
If DateValue({Brokerage.ExtendDate2}) <> CurrentDate then
{Brokerage.ExtendDate2} else
If DateValue({Brokerage.ExtendDate1}) <> CurrentDate then
{Brokerage.ExtendDate1} else
{Brokerage.ExpirationDate}

The first if always works, the 2nd & 3rd if sometimes works and the default else at the bottom doesn't work.
Do I have the statement written wrong.

If I run each if separately as the formula and leave out the other parts it brings in the correct data. Why won't they work together?

Any help would be appreciated.

Thanks.

 

[lyanch]

can any of the dates be null? That will cause the formula to fail and return null when it hits one. Check for null and see if that helps.

Lisa

 

[chelseatech]

A NULL value in ({Brokerage_Offer.ReducedAmount}) will cause your formula to ignore the rest. Get the NULL out of the way first by rearranging the formula to...

If IsNull({Brokerage_Offer.ReducedAmount}) or
({Brokerage_Offer.ReducedAmount} <= 0)
then
If DateValue({Brokerage.ExtendDate2}) <> CurrentDate
then {Brokerage.ExtendDate2} else
If DateValue({Brokerage.ExtendDate1}) <> CurrentDate
then {Brokerage.ExtendDate1}
else {Brokerage.ExpirationDate}
Else
{Brokerage_Offer.ExpireDate}

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[dgillz]

Another option to catch nulls is under file, options, on the reporting tab, click the checkbox "convert null field values to default".

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com