Date Interval

[lerwane]

I have a query which computes for date interval.

Then in the report, I used the function SUM basing from the query
to show the total_years and total_months.

The problem now is in total_months, because if it exceeds or equal to 12, the total_years should increase by one (1) then the total_months will now be zero (0).

e.g.

total_years: 5 total_months: 15

the correct output should be:

total_years: 6 total_months: 3


How will I do this?

Please help!

 

[Mile-O]

The SUM function? Are you sure that's what you want?

Anyway, only calculate the months.

Youc an then get the years from:

=Int(Months/12)

And you can get the months from:

=Months Mod 12

 

[lerwane]

this is the scenario:

in qryDate, it shows like this way:

id name byear bmos

1 sara 2 10
1 sara 2 5
2 jay 1 3
....

then the next thing to do is I want to get the total years for every user.

what i did is, in my report the field for total_year i put this function
=Sum([qryDate]![byear]) then for total_mos =Sum([qryDate]![bmos])

basing from the data above, the output are the ff:

total_year = 4 yrs. total_months = 15 months

that is the problem now, coz it should be:

total_year = 5 yrs. total_months = 3 months

so basing from the scenario, what should i do?

please help

 

[Mile-O]

so basing from the scenario, what should i do?

I've already told you the easisest way to count years/months. I'll also through in the the DMin() and DateDiff() functions which can be referenced in the help files.

 

[lerwane]

Yes, I used the DateDiff function in getting the interval.

I have several records for every user that's why I wanted to get the sum of all intervals. But now, its working. I used the functions that you gave.

Thanks Mile