Calculation Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there is an easy way to calculate a month parameter. For example. If you
have a date differance between a start date and end date and you want to know
the number of months inbetween.

Secondly. Is there a way in a query or report to total all those who fall
between 0 and 12 months, 16 and 36 months, and 37 and 60 months.
 
First problem is to define the rule on months bewteen two dates.

datediff("m",#7/31/2007#,#8/1/2007#)
And
datediff("m",#7/31/2007#,#8/31/2007#)
Both return 1 when you ask for the difference in months; however, the first
returns 1 and the second 31 when you change the "m" to "d"

This, of course leads to the 28/29/30/31 issue.

So, the first order of business is to determine a rule for what a month is.

As to separating the results by number of months, that is the easy part.

You will need to use some Calculated Fields in your query. The first will
be the calculation to return the number of months. For example purposes, I
will assume using the raw months returned is okay.

M0to12: IIf(datediff("m",#[StartDate]#,#[EndDate]#) Between 0 and 12,1,0)

M16to36: IIf(datediff("m",#[StartDate]#,#[EndDate]#) Bewteen 16 and 36,1,0)

M36to60: IIf(datediff("m",#[StartDate]#,#[EndDate]#) Bewteen 36 and 60,1,0)
 
Back
Top