ms access - Using DatePart() with date ranges crossing the datepart boundary -

i trying summarise data tables report. each record in table consists of date range, this:

startdate    enddate -------------------- 13/04/13     15/04/13 17/04/13     24/04/13 28/04/13     03/05/13 05/05/13     10/05/13 

assuming date ranges signify days of leave, want able calculate total amount of days of leave per month. came across datepart function seems work apart 1 edge case: when date range crosses month boundary. since datepart function returns month 1 given date, no longer able use determine amount of days of leave edge case record (in example above record 3), since applies 2 separate months.

ideally want final table like:

 month       #ofdays --------------------    4           11               (1st record - 2, 2nd record - 7, 3rd record - 2)    5           8                (3rd record - 3, 4th record - 5) 

i've considered messy options, such populating temporary table having each record signifying different day , doing query on that, not sure how ties in report. right report record source (incorrect) query, possible have record source vba function returns recordsource?

another thing thought possibly have initial query splits edge cases 2 seperate records, date range covers 1 month, , use final grouping query. possible?

i feel there may simpler solution problem yet can't see it.

if has ideas appreciated!

to accomplish task using access queries need create table named [numbers] single number (long integer) column named [n] containing numbers 1, 2, 3, ... highest year expect working with. created mine follows

   n ----    1    2    3  ... 2499 2500 

you'll need paste following vba function access module

public function isvaliddayofyear(yearvalue long, dayvalue long) boolean dim isleapyear boolean if (yearvalue mod 400) = 0     isleapyear = true elseif (yearvalue mod 100) = 0     isleapyear = false elseif (yearvalue mod 4) = 0     isleapyear = true else     isleapyear = false end if isvaliddayofyear = (dayvalue <= iif(isleapyear, 366, 365)) end function 

let's assume source table called [dateranges]. we'll start creating query generates every day of year each year represented in source table. trick here dateserial() "rolls over" month boundaries, so

dateserial(2013, 1, 32) = #2013-02-01#

and

dateserial(2013, 1, 234) = #2013-08-22#

select dateserial(yr.n, 1, dy.n) [date] numbers yr, numbers dy      (         yr.n              between (select min(datepart("yyyy", dateranges.startdate)) dateranges)                 , (select max(datepart("yyyy", dateranges.enddate)) dateranges)     )     , (dy.n < 367) , isvaliddayofyear(yr.n, dy.n) 

for sample data, query returns days in 2013.

let's save query [alldays]. can use extract individual days each date range (omitting startdate final counts match yours in question)

select [date] alldays exists      (         select * dateranges          alldays.[date] between dateadd("d", 1, dateranges.startdate) , dateranges.enddate     ) 

that returns individual days corresponding each range, i.e.,

date       ---------- 2013-04-14 2013-04-15 2013-04-18 2013-04-19 2013-04-20 2013-04-21 2013-04-22 2013-04-23 2013-04-24 2013-04-29 2013-04-30 2013-05-01 2013-05-02 2013-05-03 2013-05-06 2013-05-07 2013-05-08 2013-05-09 2013-05-10 

we can save query [rangedays] , use calculate our counts month...

select datepart("m", [date]) [month], count(*) numofdays rangedays group datepart("m", [date]) 

...returning

month  numofdays -----  ---------     4         11     5          8