Calculate number of days between parameters

[KatyB]

I have a contract management database where contracts have a start date and an end date. I also have a date parameter set up whereby I can show live contracts between overlapping records.

I want to be able to count the number of days for each contract that is live between the dates of the parameters.

For example my contract might run from 1st to 30th November but I might want to report from 10th October to 10th November so the number of days I need the query to return is 10 days as my contract is not live before the 1st November. Or my query might run from 1st October to 31st December, then I would need it to show 30 days as it covers the whole of this contract.

Is it possible to do this please?

 

[JHB]

Show some sample data in a Excel sheet or in a MS-Access database, zip it.

 

[CazB]

Try something like this... subsituting your own table / field names, of course...

PARAMETERS [Start Date?] DateTime, [End Date?] DateTime;
SELECT mytable.ContractStart, mytable.ContractEnd, DateDiff('d',IIf([ContractStart]>[Start Date?],[ContractStart],[Start Date?]),IIf([ContractEnd]>[End Date?],[End Date?],[ContractEnd])) AS Dayslive
FROM mytable
WHERE (((mytable.ContractStart)<[End Date?]) AND ((mytable.ContractEnd)>[Start Date?]));

 

[KatyB]

This is great thank you so much - I just have one more question.

If I enter date parameters (for example) as 1st Feb 2014 to 28 Feb 2014 I get the difference in days as 27, not 28. Please do you know how to resolve this?

Many thanks