Writing a formula for dates 1

[akaivyleaf]

I want to be able to show records from the database where the beginning date is between 04/01/01 and 6/30/01 and exclude those records that in that date range that have an ending date that is greater than 3/30/01.

I can of get the report to work with either of these statements but not with both. In other words I can get a report to print that shows all beginning dates between the date ranges, or I can get the report to exclude those that fit the ending date criteria but not both.

Could someone explain what I am doing wrong?

Thanks in advance,

Sharon

 

[balves]

Not knowing your database, but guessing that the ending date > beginning date for each record, aren't your 2 clauses mutually exclusive? In other words, won't every record that has a beginning date of 4/1 or greater also have an ending date of at least 4/1?

 

[Ngolem]

How are you making the date comparisons??? I do not understand the phrase

"where the beginning date is between 04/01/01 and 6/30/01 and exclude those records that in that date range that have an ending date that is greater than 3/30/01."

 

[akaivyleaf]

Maybe I need to modify my original request. In light of what was said above I wouldn't have to use a begin date at all when I'm trying to find all those person who don't have an end date at all or ended 4/1/01 or later.

How would I write that? This is my formula so far which returns all the nulls. Again I can do one or the other, return all the nulls or return those who have and ending date after 4/1/01 but not both at the same time. That is now my question...

{SERVICE.NAME} = "SENIOR COMM. SERVICE EMPLOY. PROGRAM" and
{ELIGIBILITY.PROGRAM_CODE} = 381.00 and
isnull({ELIGIBILITY.END_DATE})

How do I modify this to show both?

 

[jjames]

{SERVICE.NAME} = "SENIOR COMM. SERVICE EMPLOY. PROGRAM" and
{ELIGIBILITY.PROGRAM_CODE} = 381.00 and
( isnull({ELIGIBILITY.END_DATE}) OR {ELIGIBILITY.END_DATE} >= Date(2001,4,1) )