Evaluating Null Dates

[DocFKNx]

I've seen several messages on this topic on the web - nothing I've seen has worked so far. I'm completely unable to evaluate a NULL date with SQL 2000 as the data source. I've tried several techniques including:

Date( {MR_MEMBER_1.NAR_JOINDT} ) = Date(0,0,0)

This evaluates to FALSE when there is a date present but evalautes to blank if the date is null. Well actually I'm not sure what it evaluates to if the date is NULL because if you later check it with:

If ToText( Formula_Above ) = "" Then
Formula = "BLANK"

it still doesn't evaluate. So without all this trial and error the real question is HOW DO YOU EVALUATE A SQL DATE FIELD FOR NULL VALUES? ; )

Thanks!

X

PS Why isn't there a forum here for a version of Crystal higher than 4.0? It's up to 9.0 now! Or is THIS that forum? ; )

 

[GJParker]

This Forum is for any and all versions of crystal reports The 4 in the forum name is because there are 3 other Crystal Reports forums - 1 = Formulas, 2 = Data Access and 3 = Integrate.

I tried your formula

Date( {MR_MEMBER_1.NAR_JOINDT} ) = Date(0,0,0)

and this worked fine in version 9 against my SQL Server 2000 database returning TRUE for Null dates and false for good dates. Are you sure they are actually null values you are testing for and not just blank strings ?

you could also try

isdate(cstr({MR_MEMBER_1.NAR_JOINDT}))

This should return a value of false for anything that isn't a date

Hope this helps Gary Parker
Systems Support Analyst

 

[DocFKNx]

I was poking around in Report Options and found my "Convert NULL field to Default" was unchecked. I checked it and that formula started working for me like it did for you.

Still need to find out what that really does but for not it seems to be a solution to my dilema which is GREAT!

Thanks!

X