Need to search a range within a range 1

[bendixen]

Any help on this would be appreciated. I'm losing what little hair I have left!

I am trying to query a range within a range. I have a table which has a ProjStartDate and a ProjEndDate field. There is also an AnnualSalary field. My report has a RptStartDate and a RptEndDate. I am trying to have the report show the salary calculation for any projects with dates within the range of RptStartDate and RptEndDate.

I've calulated the monthly salary (AnnualSalary/12). I've calculated the number of months between a range to multiply by the monthly salary. This part works fine.

My query is set so the start date is StartDate: IIF([RptStartDate]>[ProjStartDate],[RptStartDate],[ProjStartDate]) and the end date is EndDate: IIF ([RptEndDate]<[ProjEndDate],[RptEndDate],[ProjEndDate])

This works well for any projects that have start or end dates that fall withing the report range. What it is failing to pickup is any project that has larger date range than the report date range. (It won't pick up a project with a range of 1/1/05 - 12/31/06 if the report range is 1/1/06 - 6/30/06)

Ultimately what I want is the salary for each project for just the months within the report date ranges. Help!?

 

[Gavona]

This formula will give you the number of days the project has been active within the report period. It is a formula you would put in a cell in the worksheet. Syntax may be different in a query.

From your post it would appear that you would use a monthly rather than daily salary - not sure how you would calculate the salary cost for eg
2 weeks in February (14/28*monthlySal?)
or
2 weeks in july (14/31*monthlySal?).
This could require a more complex formula!

Hope this helps a little!


Gavin

 

[Gavona]

Oops, here is the formula!

=IF(AND(ProjStartDate<RptEndDate,ProjEndDate>RptStartDate),MIN(RptEndDate,ProjEndDate)-MAX(RptStartDate,ProjStartDate),0)


Gavin

 

[bendixen]

Gavin,

Thanks for that solution. It works great in Excel, but not so great in Access. I'm trying to change it to work, but not having success yet.

Thanks for helping me look at it differently. Using a daily rate makes much more sense than trying to use a monthly rate. Also, I hadn't thought of using MIN and MAX functions on this. Hopefully I'll get it figured out soon.

Tom