include those records that have Null End Dates

[Former Member]

I have to pull a report that show those who are currently working so we are assuming those with no End Dates are still working. When I put '0' or '0/0/00' for the End Date I get an error stating I need to put in a date.

How do I only include those records that have Null End Dates? Ultimately I need a count of those currently employed. So I'm assuming once my report only shows those records I can add a running total field.

I have Crystal XI.

Thanks

[Former Member · Accepted solution]

Try entering some default value for null like enter a default date "1/1/1900" which means that the end date is null.

Now in the record selection write the condition like this

if {?EndDateParameter}=date(1900,1,1) then
(isnull(datefield)=true or totext(datefield)="")
else
datefield={?EndDateParameter}

Regards,

Raghavendra

[Former Member · Accepted solution]

Raghavendra

Thanks, under selection expert for the end date I selected "less than or equal to"

'1/1/1900' and that seemed to do the trick. It is only shows one record for each individual, those with only the Null end date. I did not apply the if, then condition

what does this do?

[Former Member · Accepted solution]

The if else statement will check whether the entered parameter value is "1/1/1900" or not. If yes then check for nulls if not then get values lessthan end date

Regards,

Raghavendra

[Former Member]

In the formula design studio, in the top right, there is a dropdown, by default it says 'exceptions for nulls'. That needs to be changed to 'default values for nulls'. Then, isnull(<date field>) Should make sense. Generally, I try to avoid the global conversion of DB nulls to default values, and do it only in specific formula evaluation.

[Former Member]

Ryan

I finally found the formula designer and I made the change as stated.

I then used the isNull (date_field) and in trying to figure out where to place this formula field I tried it in the Details section and Header and it just says 'False' in both places. I'm a newbie at crystal so excuse my not so smart moves.

I was hoping this would show me only one record (the latest record) which would be the one with the Null End Date.

Thanks

[Former Member]

In the formula for your select statement, you need to ensure that 'default values for nulls' is selected, and then you can use the isnull(<yourdate>) to include records with null dates explicitly. That is, assuming it's stored as a date field in the database.

If not, have a look in the CR help files for default values for nulls, and determine which evaluation you should use to explicitly include them.

[Former Member]

Hello

When you say "you need to ensure that 'default values for nulls' is selected" are you talking about selected in the File > Report Options?

If so, Under Report Options I have selected: "Convert DB NULL Values to Default and Convert other Null Values to Default"

This is a subreport, does that matter?

Also, I did try the isNull(date_field) and I go 0 records displayed.