Syntax for testing for an empty SQL Sever datetime field in CR formula

[troysf]

In a CR formula, I try to test for an empty (not null)datetime field in a SQL Server table. I try the following syntax:

If {mytable.datetime1} = date (0,0,0) then...

and

If not (DayeOfWeek{mytable.datetime1} in 1 to 7 )then ...

None of these work. Does anyone knows the correct syntax?
Thanks.

Troy
San Francisco

 

[kenhamady]

Try:

if {field} < DateTime(1,1,1,0,0,0)
then .... Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[troysf]

Thanks Ken, for your suggestion.

I just tried this in a formula that is used on the detail section of a CR version 7 report:

If {mytable.datetime1) < DateTime(1,1,1,0,0,0)then
// to stuff empty field with today's date
CurrentDate
else
{mytable.datetime1)
...


However, the empty fields still return blanks. The non-fields do return correctly. Looks like the empty fields are by-passing the if-then-else statement!!! I am still experimenting.

Regards,
Troy

 

[kenhamady]

What happens if you use:

If IsNull({mytable.datetime1})
then CurrentDate
else {mytable.datetime1) Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal