Evaluating a empty date field with Crystal Syntax

I am trying to populate a field with one of three values based on this if statement.  Everything seems to evaluate properly except the "{@datecompleted}=Date (0,0,0)".  I thought this was the correct way to check for an empty date field but I guess I am wrong.  Can anyone help me out?

if ({@datecompleted} > {@daterequired}  )
then
"CLate"
Else if{@daysout}>60 and {@datecompleted}=Date (0,0,0)
then
"NLate"
Else
"On Time"
ccarlson62Asked:
Who is Participating?
 
jswebyConnect With a Mentor Commented:
I have successfully used the IsNull test before, however it MUST be the FIRST test done on that field, so intead of

if {@daysout}>60 and IsNull({@datecompleted})

use

if IsNull({@datecompleted}) and {@daysout}>60

J.
0
 
DRRYAN3Commented:
ccarlson62

Try one of the following:

Else if {@daysout}>60 and {@datecompleted} <= date(0,0,0)

or this one

Else if {@daysout}>60 and {@datecompleted} <= date(0,0,0,0,0,0)

Depending on your database, you may need to check to see if the date is LESS THAN a null date value instead of EQUAL TO.  You may also need to explicitly null the time components of a date-time variable by including the extra 3 zeros.

DRRYAN3
0
 
mlmccCommented:
You might also have to check as

Else if {@daysout}>60 and IsNull({@datecompleted})

mlmcc
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ccarlson62Author Commented:
Thanks for the suggestions

I Have tried:

Else if {@daysout}>60 and {@datecompleted} <= date(0,0,0)

and this one...

Else if {@daysout}>60 and {@datecompleted} <= date(0,0,0,0,0,0)

and this one....

Else if {@daysout}>60 and IsNull({@datecompleted})

No luck so far, I am upping the points to 100 since this  does not look like it is going to be as simple as I had hoped.

0
 
DRRYAN3Commented:
What database is the source of your data and which CR driver are you using to get to it?  Also, what version of CR do you have?
0
 
ccarlson62Author Commented:
I am using Crystal 8.5

I am using the Notes SQL Driver ver. 3.01

I am going after Data in a Domino Database Domino server ver. 5.0.10
0
 
DRRYAN3Commented:
I don't have Domino to test against.  

Perhaps Domino is not storing empty dates as a null value.  I searched the Domino Developer web site without success on this.  What value is in @datecompleted for a record you know to have a null value?

Last question - how have you declared the @datecompleted variable in your Crystal formula?
0
 
gawilson2000Commented:
I have had a lot of trouble with null dates before too.

So I just check for:
ToText({@datecompleted}) = ''
0
 
gawilson2000Commented:
You should be very wary with this solution.  

Another programmer may come along at a later date and add more code at the beginning.

I think that you will find that my code works regardless of the position.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.