Populate a null date field with a value

[purefreshair]

Hello,

I am using Crystal Reports 8.0. I have a table from an SQL server database that contains an "edate" and "edateto" values which have a format of xx/xx/xxxx. I want to be able to define a new formula field to test the "edateto" value; if it is null I want my formula field to have a value of 12/31/2010; else use the value of "edateto". I've tried this syntax but get an error in the formula:

IF NULL {tablename.EDATETO} THEN '12/31/2010'

I believe the formula does not the format of what I am trying to use; does antyone know what I'missing?

Thanks,

 

[Madawc]

I've only used 8.5 and 10, but it ought to work with IF ISNULL ({tablename.EDATETO}) THEN DATE(2010, 12, 31) ELSE {tablename.EDATETO}

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[purefreshair]

Thanks Madawc!

I getting closer, however upon trying your example, I get an error that reads:
A number, currency amount, boolean, date, time, date-time, or string is expected here.

I'm wondering if the field is actually a date-time field and if so, would your example also have to include a time value such as '000000' and if so, how?

 

[lbass]

If you roll your mouse over one of your datefields placed in the detail section, you will see the tooltip which will tell you the actual datatype. Or you can right click on the field->browse to determine the datatype. Then report back.

-LB

 

[purefreshair]

Hi there lbass,

It is a datetime datatype.

 

[lbass]

Then try:

IF ISNULL ({tablename.EDATETO}) or
{tablename.EDATETO} = datetime(0,0,0,0,0,0) THEN
DATETIME(2010, 12, 31,0,0,0) ELSE
{tablename.EDATETO}

-LB

 

[purefreshair]

That's it!! That worked! Thanks so much.