Need to return a date field that is blank

[mjstanton]

Using CR8.5

I am trying to create a repoprt that lists employees that include their start dates and end dates. I only need the people who started after 8/01/2000 whose end dates are after 10/31/2007. But I also need to include in this list current employees whose end date field is blank. I am a very unexperienced user, and I'm having a hard time figuring out how to do this. Any help would be greatly appreciated!

TIA,
MJ

P.S. If it makes a difference, I am building this report from a Lotus Notes database.

 

[mocgp]

Try

StartDate>8/1/2000 and (IsNull(EndDate) or EndDate>10/31/2007)

 

[mjstanton]

Thanks, mocgp!! I see now that I should have been a bit clearer. The report needs to look like this:

COMPANY EMPLOYEE START DATE END DATE
XYZ, Inc Sally Jones 9/30/2004 11/07/2007
XYZ, Inc John Smith 10/17/2006
XYZ, Inc Barry White 10/31/2005 01/04/2008

So, in the above example, Sally and Barry are no longer employed, but John is. I need the end date field to just be blank if they are still employed. I have no problem with the start date, it's the end date that's giving me a headache!

Thanks, again.
MJ

 

[lbass]

mocgp gave you the record selection formula. Once you have that, you just need to place the fields on your report, and they should populate correctly.

If they are not populating correctly, I would guess that you are using more than one table, and that you would need to change the join from the main employee table to the table containing the hire/end dates to a left outer join.

-LB

 

[mjstanton]

Thanks, LB

I am using this formula for the end date:

{Form: Billing|Cons_End_Date} > Date (2000, 7, 31) OR
IsNull({Form: Billing|Cons_End_Date})

This returns "TRUE" when there is an end date, and is blank when there is no end date. The blank part is what I want for current employees. Now how do I change "TRUE" to the actual end date?

Thanks again,
MJ

 

[lbass]

You MUST check the null first before checking the field value, as in:


StartDate>8/1/2000 and
(
IsNull({Form: Billing|Cons_End_Date}) or
{Form: Billing|Cons_End_Date} > Date (2000, 7, 31)
)

This is a record selection formula (report->selection formula->record). Then just place the end date field on the detail section and it will display correctly.

-LB