OR statement being ignored

[Burles]

Hi

Looking for some formula advice if possible in Crystal Reports 9.5.

I have created a report with a date parameter field ({?Expiry Date}), and am looking for the report to find records where the description includes the letters ext AND they have either a temporary expiry date OR renewal date that is equal to the date parameter that the user enters. Formula that I am using is below:

{Licenses.Description} like "*ext*" and
({Licenses.TempExp}={?Expiry Date} or {Licenses.RenewalDate}={?Expiry Date})

The problem that I am finding is that my OR command seems to be being ignored. With the above I find anything where the description contains ext and TempExp is equal to the {?Expiry Date}.

If I reverse the formula to:

{Licenses.Description} like "*ext*" and
({Licenses.RenewalDate}={?Expiry Date} or {Licenses.TempExp}={?Expiry Date})

I find anything where the description contains ext and RenewalDate is equal to the {?Expiry Date}. I just cannot find both.

Any help of advice would be greatly appreciated before I rip all my hair out.

 

[fisheromacse]

i am a bit crazy about using parens for grouping.
i think your logic is good, but would have written it like this:

(
({Licenses.Description} like "*ext*")
and
(
({Licenses.TempExp}={?Expiry Date})
or
({Licenses.RenewalDate}={?Expiry Date})
)
)

 

[IanWaterman]

I agree your logic looks good.

Are you sure you are not restricting selection to top N and you just happen to see results as it finds them and never gets to the other set.

Failing that try
(
({Licenses.Description} like "*ext*" and {Licenses.RenewalDate}={?Expiry Date})
or
({Licenses.Description} like "*ext*" and {Licenses.TempExp}={?Expiry Date})
)

Ian

 

[Burles]

Thanks guys....

I don't I am think restricting results as if I just use {Licenses.Description} like "*ext*" I get pages of results. Unfortunately with both suggestions I am still getting the same result. I have just replaced the OR with and AND and get results as I would expect if I was looking for this criteria but as soon as I add the OR back it ignores the last statement.

I have just gone crazy to try and cover all bases and ensure that null values are not causing the problem but still to no avail.
(
(
{Licenses.Description} like "*ext*"
)
and
(
(
{Licenses.RenewalDate}={?Expiry Date}
and isnull
(
{Licenses.TempExp}
)

)
or
(
{Licenses.TempExpiry}={?Expiry Date}
and isnull
(
{Licenses.RenewalDate}
)

)
or
(
{Licenses.RenewalDate}={?Expiry Date}
and not isnull
(
{Licenses.TempExp}
)

)
or
(
{Licenses.TempExp}={?Expiry Date}
and not isnull
(
{Licenses.RenewalDate}
)

)
)
)

I am lost................

 

[MCuthill]

Burles,

If Nulls, those generally need evaluated first in a logical statement. IsNull({SomeField}) Or .... That being said, I don't know that it is the problem (I don't think it is...)

In taking the idea of expanding the logic, for the sake of testing, does the following correct the logic?

(
{Licenses.Description} [blue]LIKE[/blue] "*ext*" [blue]AND[/blue]
{Licenses.TempExp}={?Expiry Date}
)
[blue]OR[/blue]
(
{Licenses.Description} [blue]LIKE[/blue] "*ext*" [blue]AND[/blue]
{Licenses.RenewalDate}={?Expiry Date}
)

or perhaps:

(
"EXT" [blue]in UpperCase[/blue]({Licenses.Description}) [blue]AND[/blue]
{Licenses.TempExp}={?Expiry Date}
)
[blue]OR[/blue]
(
"EXT" [blue]in UpperCase[/blue]({Licenses.Description}) [blue]AND[/blue]
{Licenses.RenewalDate}={?Expiry Date}
)

If this does not correct the issue, might you be able to provide some sample data / results to give us a better idea what the data we are working with?

Hope this helps! Cheers!


Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[hilfy]

The problem is definitely the null values. When you compare anything to null, you get null, not True or False.

Here's how I would rewrite it:

{Licenses.Description} like "*ext*"
AND
(
(not IsNull({Licenses.TempExp}) and {Licenses.TempExp}={?Expiry Date})
OR
(not IsNull({Licenses.RenewalDate}) and {Licenses.RenewalDate}={?Expiry Date})
)

-Dell


DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Madawc]

you can also split the tests between several formula fields. Invoke them by name in another formula field. And maybe display them in a test line during development to see if you have what you thought you'd got.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Burles]

Hi All

Thanks for your help and advice on this one. Sorry for the lack of response from me. I have been out of the office. Came back to have another go taking everyone's advice this is what finally worked for me.

{Licenses.Description} like "*ext*"
and
(
(isnull({Licenses.RenewalDate}) and {Licenses.TempExp}={?Expiry Date})
or
(isnull({Licenses.TempExp}) and {Licenses.RenewalDate}={?Expiry Date})
or
{Licenses.RenewalDate}={?Expiry Date}
or
{Licenses.TempExp}={?Expiry Date}
)

It seems that the isnull must come first, as if I put them last the formula stops working.

 

[Madawc]

That's right. Any null field will stop the formula, unless IsNull is used to allow for it.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP