Selecting where date field is NULL

[ctwilliams]

My report pulls from a view that contains an outer join on the date field... some of the items I'm reporting on have a date, others do not. I want to display all items regardless of the date, however if the item DOES have a date I want to be able to restrict it using a {?DaysBack} parameter. Here is my selection formula...

DateDiff (&quot;d&quot;,{rpt.MyDate},CurrentDate) <= {?DaysBack}

This formula works in restricting the date by the {?DaysBack} parameter, however it is not reporting the items that have NULL dates. I tried adding the following but it didn't work...

DateDiff (&quot;d&quot;,{rpt.MyDate},CurrentDate) <= {?DaysBack}
OR IsNull ({rpt.MyDate})

 

[dgillz]

Put your null test first.

Also, go to file, options and make sure the &quot;Convert null field value to default&quot; option is NOT checked.

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[ctwilliams]

That worked! Thanks so much.

 

[synapsevampire]

dgillz: It matters that the null test is first??? I've never observed that, do you have an example? I couldn't get a different result.

re: &quot;DateDiff (&quot;d&quot;,{rpt.MyDate},CurrentDate) <= {?DaysBack}
OR IsNull ({rpt.MyDate})?&quot;

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

I h ave never experienced this either, but I have been told it matters on certain databases. Every time this question comes up the answer has been to test for nulls first. Usually, that works. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

Hmmm, thanks for the scarey thought...

If you come across an example, please forward it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

I have also, particularly in btoreve databases, found inconsistent results with IsNull(). I have had to write a formula like the following instead:


IsNull({rpt.MyDate}) or
Length({rpt.MyDate})=0 or
DateDiff (&quot;d&quot;,{rpt.MyDate},CurrentDate) <= {?DaysBack}

Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

OK, thanks for the response, dgillz.

I think that you may be experiencing differences in how data is stored and/or returned from various data sources.

The obvious one that you know is the convert null to default setting, but also ODBC drivers can produce different results.

I'd like to document any examples you might come across.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com