Null Value for Date Field

[haeddy]

I'm trying to create a report that will show the most current health and dental insurance coverage our employees have. The report is perfect except it is showing employees that no longer have coverage and I can't get it to eliminate these employees. I have a Database Field called Coverage End Date and I think it will work if I can tell the report to only show records that have a null Coverage End Date value but I can't figure out how to do that.

I'm using two tables, one with employee information and one with benefit information and the tables are linked by employee ID number (EMPLID).

The Select Expert group formula I used is:

{PS_HEALTH_BENEFIT.EFFDT} = maximum ({PS_HEALTH_BENEFIT.EFFDT},{PS_HEALTH_BENEFIT.EMPLID})

Thanks to anyone out there that can help!

 

[SkipVought]

Hi,

Do you have an Employee Status in the Employee Information table?

 

[Charliy]

if isnull({coverageenddate})
then something
else somethingelse

 

[haeddy]

Yes SkipVought. I have an Employee Status in the table.

 

[haeddy]

This is the Select Expert Record formula I'm using.

{PS_HEALTH_BENEFIT.COVERAGE_ELECT} = "E" and
{PS_ALL_EMPLOYEES.AL_EMPL_STATUS} = "A" and
isnull({PS_HEALTH_BENEFIT.COVERAGE_END_DT})

 

[kray4660]

Always check for nulls first.

isnull({PS_HEALTH_BENEFIT.COVERAGE_END_DT}) and
{PS_HEALTH_BENEFIT.COVERAGE_ELECT} = "E" and
{PS_ALL_EMPLOYEES.AL_EMPL_STATUS} = "A"

It may not make a difference, but Crystal is funny about checking for Nulls (also make sure that the Default Values for Nulls is selected in the Formula Workshop)