Need Help querying range within range using MIN & MAX 1

[bendixen]

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 a DailySalary 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.

My query is currently 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 days within the report date ranges. Another user gave me a solution that works in Excel (THANKS GAVONA)...

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

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.

...but I'm having a difficult time converting it to work in Access. Would anyone smarter than me help figure this out? Thanks so much!

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bendixen]

SELECT IIf(and([ProjStartDate]<[RptEndDate], [ProjEndDate]>[RptEndDate]),MIN([RptEndDate],[ProjEndDate])-MAX([RptStartDate],[ProjStartDate]),0) AS DaysInReport
FROM qry_ProposedEffort1b;

This gives me an error of "Syntax error (missing operator) in query expression".


I'm guessing the solution will have to be multiple queries that produce my desired result via table joins, but I'm hoping that there is a simple way to do this instead.

 

[PHV]

What about this ?
SELECT IIF([RptStartDate]>[ProjStartDate],[RptStartDate],[ProjStartDate]) AS StartDate
, IIF ([RptEndDate]<[ProjEndDate],[RptEndDate],[ProjEndDate]) AS EndDate
FROM qry_ProposedEffort1b
WHERE ProjStartDate<=[RptEndDate] AND ProjEndDate>=[RptStartDate]

BTW, DaysInReport may be easily calculated in the report itself.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KyleS]

Hi,

The Min/Max functions work far differently in JetSQL than they do in Excel, so give this query a run

SELECT Switch([ProjStartDate]<[RptEndDate] And [ProjEndDate]>[RptStartDate],Switch([ProjEndDate]<[RptEndDate],[ProjEndDate],True,[RptEndDate])-Switch([ProjStartDate]>[RptStartDate],[ProjStartDate],True,[RptStartDate])) AS DaysInReport
FROM qry_ProposedEffort1b

Kyle

 

[bendixen]

Thanks PH, that works great (and gives me something to study and learn from).

Tom

(BTW, the DaysInReport is in the query so I can use it in other calculations)