Return All Date Ranges

[Guest]

I have a table with a StartDate field and an EndDate field. I need to create
a query with calculated fields and the query needs to return the date ranges
in the table as well as missing date ranges. The first calculated field,
"IsNotOpen", needs to show T if the date range is in the table and F if the
date range is not in the table. The second calculated field, "IsNotOpenNum",
needs to sequentiially number the date ranges in the table. The third
calculated field, "IsOpenNum", needs to sequentiially number the date ranges
that are not in the table. The data in the table looks like this:
StartDate EndDate
2/1/06 2/3/06
2/6/06 2/8/06
2/9/06 2/9/06
2/10/06 2/15/06
2/20/06 2/23/06
2/24/06 2/25/06
2/27/06 2/28/06

The data in the query needs to look like:
StartDate EndDate IsNotOpen IsNotOpenNum IsOpenNum
2/1/06 2/3/06 T 1
2/4/06 2/5/06 F
1
2/6/06 2/8/06 T 2
2/9/06 2/9/06 T 3
2/10/06 2/15/06 T 4
2/16/06 2/19/06 F
2
2/20/06 2/23/06 T 5
2/24/06 2/25/06 T 6
2/26/06 2/26/06 F
3
2/27/06 2/28/06 T 7

Thanks!

 

[Guest]

I have a table with a StartDate field and an EndDate field. I need to create

a query with calculated fields and the query needs to return the date ranges
in the table as well as missing date ranges.

Didn't you say you hired PC Datasheet to do several projects for you? He
could do this query for you in a snap. Maybe he's too expensive. Contact
us. Our fees are very reasonable.

It's faster and usually cheaper to hire an expert. If you don't think it's
cheaper, how much is your time worth where you could be doing productive work
instead?

Chris Mills
Your Access Resource

http://www.accessresource.somee.com/Specials.html