Frustrating Issue with Dates and Nulls (null/blank dates)

[cheerfulskeptic]

I have a table with the field "Inactivedate" (SQL 2000, datetime type). This is usually blank for most records, but I need to access the field in a CR8 report to determine in a running total formula whether to count the current record or not.
Unfortunately, I have tried everything (isNull, = "", Date(0,0,0), and nothing seems to work.
I have heard this is a common issue - what is the resolution?
For example, if I have a running total field whose evaulation formula is the following...

{tbl_SystemWarranty.fld_installdate} <= Date ({?Year}, 05, 31)
and
cdate ({tbl_SystemWarranty.fld_inactiveDate}) < Date ({?Year}, 05, 31)
and {tbl_SystemWarranty.fld_isESIowned} <> &quot;Y&quot;

What is the problem here? The inactive date does not even work if I do a simple formula like

if cDate ({tbl_SystemWarranty.fld_inactiveDate}) <> Date(0,0,0) then ToText ({tbl_SystemWarranty.fld_inactiveDate}) else ToText (&quot;blank&quot
Thanks

 

[synapsevampire]

First, you need to know how the data is stored, as SQL Server has defaults, check with your DBA.

You can also browse the field within Crystal, or do a select distinct from a query tool.

So the real problem is that you may need to do more discovery.

Next, when checking for a condition of Null, do so 1st in your formulas, as in:

if isnull({table.date}) then
&quot;nothing here&quot;
else
totext({table.date})

-k