Is there a problem with this formula?

[Former Member]

I want to test for a blank date field, but isnull(date field) is not evaluating as a null when the field is, in fact, empty. I have Convert Database Null Values to Default checked because I need it checked for other purposes in my report.

As an alternative, I'm using the following formula. Is this equivalent to isnull(date field)?

If length(cstr(date field)) = 0 then "null"

Else (date field)

Is there any scenario under which this this formula would not work?

I'd appreciate any feedback.

Gary

[Former Member · Accepted solution]

Hi,

If LENGTH(TRIM(DATEFIELD)) = 0 OR ISNULL(DATEFIELD) THEN "NULL"

ELSE

TOTEXT(DATEFIELD,"FORMAT");

Regards,

Sathish

[Former Member · Accepted solution]

Sathish --

I like yours the best (sorry everybody else!).

I like checking for either the length = 0 or ISNULL, just to cover both possibilities.

I was curious why you included "trim" in the formula. What does that do?

Thanks.

Gary

[Former Member]

Hi, GL,

Null date fields should return Date(0,0,0). Check in the Help - it isn't always straightforward for the different data types.

Also sometimes worthwhile checking for empty string - ""

Good luck

[Former Member]

Just an additional note that you must always test for NULLs first (can't tell you why exactly), so something like:

if isnull(YourField) = true
or ....(other tests)

then "This Field is Null, empty, or just whitespace"

else YourField

...would be appropriate

Oh, and are you 100% sure that the field is infact a date(time) field, or a text field?

[Former Member]

I think you need to change your formula like this

If length(cstr(date field)) = 0 then "null"

Else cstr(date field)

or If you are converting default value for nulls then the default value for the date field is "". So you can try the condition like this also

If cstr(date field) = "" then "null"

Else cstr(date field)

Regards,

Raghavendra