OR is not commutative 2

[tsusnik]

The formula below is my Record Selection Formula. Both ors are ignored as if only the first part in the or statement had been written. If I therefore change the order the result is completely different. The SQL Query although looks OK.

Any suggestions are welcome!

Tomaz

{VIEW.VALID_FROM_DATE} <= {?Date} and
({VIEW.VALID_TO_DATE} > {?Date} or isnull({VIEW.VALID_TO_DATE})) and
({VIEW.VALID_TO_TIMESTAMP} >= {?Date} or isnull({VIEW.VALID_TO_TIMESTAMP}))

 

[chelseatech]

NULL data will cause problems in all sorts of ways - you might rearrange your formula with the ISNULL first

{VIEW.VALID_FROM_DATE} <= {?Date} and
(isnull({VIEW.VALID_TO_DATE}) or
{VIEW.VALID_TO_DATE} > {?Date}) and
(isnull({VIEW.VALID_TO_TIMESTAMP}) or{VIEW.VALID_TO_TIMESTAMP} >= {?Date})

or use an SQL expression - what data source are you using?

 

[kenhamady]

The IsNull check has to be first, before the field is otherwise used. This is because when a CR formula hits the null value, it chokes on it. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Ngolem]

Hi Tomaz;

IsNull's are trick at the best of times....in a Record select even more so....

try this:

(if not isNull({VIEW.VALID_FROM_DATE}) then
{VIEW.VALID_FROM_DATE} <= {?Date} and
{VIEW.VALID_TO_DATE} > {?Date}
else
True and
(if not isNull({VIEW.VALID_TO_TIMESTAMP}) then
{VIEW.VALID_TO_TIMESTAMP} >= {?Date}
else
True

 

[Madawc]

I ran into this same problem. I found that the following formula worked as expected, for a date field that would be zeros for an unceased record:

IF (ISNULL ({BAF11.Ceased}) or {BAF11.Ceased} >= Date (2002,1,1))
THEN &quot;Recent&quot;
ELSE &quot;Other&quot;

But if I tried
IF ({BAF11.Ceased} >= Date (2002,1,1) or ISNULL ({BAF11.Ceased}))
THEN &quot;Recent&quot;
ELSE &quot;Other&quot;
in the same report, all dates with zero value would be ignored Madawc Williams
East Anglia
Great Britain