Setting N/A to date fields

[jsttuchm3]

I have strange problem in setting N/A to null dates in the report.
When quering db it returns the data where date is null but the same when checked in formula CR XI doesn't set to N/A but displays some julian date instead of N/A.

Code piece is as follows

if isnull({sp.LOAD_END_DT}) then
'NUL'
else
totext({sp.LOAD_END_DT},"MM/dd/yyyy HH:mm")

The same code works fine for another date field.

I checked in the db if there is any other data apart from null but the query returns rows only for is null check.

Ideally the code should be working fine.

Is there any other way to get this to work.

Thanks

-RP

 

[lbass]

Take a look at file->report options-> and make sure that "convert nulls to default values" is NOT checked.

-LB

 

[fisheromacse]

Try adding one more 'blank' check, like below:

If isnull({sp.LOAD_END_DT}) OR TRIM({sp.LOAD_END_DT})=""
then
'NUL'
else
totext({sp.LOAD_END_DT},"MM/dd/yyyy HH:mm")

 

[jsttuchm3]

Check box is unchecked.

 

[lbass]

Well, then the database field is not null. Use the date in the formula instead, as in:

if isnull({table.date}) or
{table.date} = date(9999,9,9) then //replace with the date you are seeing
"N/A" else
totext({table.date},"MM/dd/yyyy")

-LB