Part Of Formula won't work

[Kathyrlcrs]

I am working in 9.0. with an access database.
I have this formula that I am using.


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

My first If works fine. The second if worked, but I only had one occurence(I am assuming it works.
The third and fourth if are not working.

Any suggestions as to why the last two if won't work would really help.
Thanks.

 

[Katy44]

Your line:

If {Brokerage.ExtendDate2} > CurrentDateTime or {Brokerage.ExtendDate2} < CurrentDateTime then

will pick up anything apart from Brokerage.ExtendDate2=CurrentDateTime
so it probably never gets past this line, as CurrentDateTime
will specify an exact second.
Do you mean to specify the date part of the CurrentDateTime?

 

[Kathyrlcrs]

No I do not care to specify the time, but I had a hard time comparing just the date. How do I change my field BrokerageExpirationdate to just the date and not time to compare it?

 

[Katy44]

Try DateValue({Brokerage.ExtendDate2}) - does this work?

 

[Kathyrlcrs]

Thanks for the DateValue tip. That part seems to be working now. The only part of the formula that doesn't work now is my default at the bottom.
If all the ifs fail it should be putting in Brokerage.ExpirationDate, but it is just leaving a blank.
Any suggestions?

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

 

[Kathyrlcrs]

Katy44

I thought my formula was working correctly but it is still working sporadically and also the last default else is not working.

Can you see anything wrong with the way I have set up the 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}

 

[BobJacksonNCI]

Recommend you post sample data and the desired outcome for each. Include the data for all fields. Also, I'm not sure what's going on with { } because Access uses [ ]

Here's what I see:

If Not IsNull({Brokerage_Offer.ReducedAmount}) and ({Brokerage_Offer.ReducedAmount} > 0) then
{Brokerage_Offer.ExpireDate}

If reducedamount > 0
return expiredate
End of If code
Note that if expiredate is null or blank - that's what is returned

else If DateValue({Brokerage.ExtendDate2}) <> CurrentDate then {Brokerage.ExtendDate2}

If extenddate2 is not equal to currentdate
return extenddate2 (regardless of its content)
End of If code

else If DateValue({Brokerage.ExtendDate1}) <> CurrentDate then {Brokerage.ExtendDate1}

If extenddate1 not equal to currentdate
return extenddate1 (regardless of its content)
End of If code

else {Brokerage.ExpirationDate}

If NO condition above was satisfied,
return expirationdate, regardless of its content



HTH,
Bob

 

[Kathyrlcrs]

That is exactly how it is supposed to work. However, it is not.

 

[BobJacksonNCI]

You haven't posted sample data, but I'll take a wild stab that NULL may be the problem. If so, this may be closer:

If NZ(Brokerage_Offer.ReducedAmount) > 0) then
Brokerage_Offer.ExpireDate

Else If NZ(DateValue(Brokerage.ExtendDate2)) <> CurrentDate then
Brokerage.ExtendDate2}

Else If NZ(DateValue(Brokerage.ExtendDate1)) <> CurrentDate then
Brokerage.ExtendDate1

Else
NZ(Brokerage.ExpirationDate)

My rationale is that any instance of NULL makes the comparison false. I.E., when the instruction:
If dteOne <> dteTwo
is executed, if either of them is NULL, the condition is false, NOT unequal, and the logic proceeds to the following condition to check.



HTH,
Bob