Calculate number of months between a start date and end of year

[ErikaMarie0421]

Hi.
I am now trying to count the number of months an agent was active (from contract date field) in the previous year.....as well as the current year.

These dates are stored as dates - so no formatting or conversions need to be done, I just want to know how to write an expression in design view that will tell me how many months an agent was active last year, whether his contract date was from last year, the year before, or this year (which would return a zero, hopefully).

And the same for the current year.

Any help? I don't even know where to start (been hunting online and in my access book) so I have no SQL to post on here.



Thanks again!!!

[jzwp11]

You will want to use the datediff() function. The general form of the function looks like this:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

The interval can be month, day, year etc. (check out the Access help for more detail). The last two parameters are optional. So to find the number of months between two supplied dates:


DateDiff("d", earlierdatefield, laterdatefield)

If the earlierdatefield's value is > laterdatefield's value then the function will return a negative number, if you would rather see zero, you can nest the datediff() function within an IIF() function.

[ErikaMarie0421]

The problem is, the later date is not in a column. How do I tell Access to count the number of months until 12/31 of that specific year (the year varies)?

[ErikaMarie0421]

Actually, my question is not quite clear (and slightly incorrect of what I'm looking for). So I apologize.

Allow me to explain:
I have a table with several hundred thousand agents listed by Agt ID. They have contract effective dates that go back as far as the early 1900s. The query I am trying to build is titled QRY_MONTHS_ACTIVE_PREV_YR (meaning it returns the # of months an agent was active in the previous year (previous year will obviously change from year to year, so I can't just use 12/31/2009 as the LDate unless I want to go in and update each year, which I'd rather not).

So, my query as of now will return the Agt_ID, Car_Eff_Date.....and the next column I want to automatically count the number of months each agent was active last year. So, if the Car_Eff_Date was from prior to 2009, it would return "12,", if the Car_Eff_Date was 4/1/2009 I would like it to return "8."

Is this possible? Thanks again. I appreciate any help!!

[jzwp11]

Yes, it is possible, but I have to make a correction, the function example I provided in my earlier post would return the number of days ("d"), to return the number of months the interval should be "m".

You will need to do some evaluations and use some other functions to get to the solution.

Case 1: agent contract date is occurred prior to last year
Case 2: agent contract date occurred sometime last year

We can use the dateserial() function to construct the previous year's start and end dates from the current date

dateserial(year(date())-1,1,1) will give us January 1, 2009
dateserial(year(date())-1,12,31) will give us December 31, 2009

To do a test, we need the IIF(expression, true part, false part) function.

Basically if the Car_Eff_date is less than January 1st of last year

IIF(Car_Eff_Date<dateserial(year(date())-1,1,1), datediff("m",dateserial(year(date())-1,1,1), dateserial(year(date())-1,12,31)), datediff("m",Car_Eff_Date,dateserial(year(date())-1,12,31)))

Hopefully, I have put in all of the parentheses... I'll leave that for you to test.

[ErikaMarie0421]

Well - great work! It is working...just not exactly accurate. If someone was contracted on 1/1/09, it's still only returning '11' instead of '12' - but if they were contracted on 1/1, they had 12 full months active with us last year. Any way around this?

[ErikaMarie0421]

I would also like to calculate the number of months the contract has been active in the current year. I know this expression would be very similar, but not having too much luck as of yet.

[jzwp11]

I was afraid of that. The function will return an integer, thus ignoring a partial month, so I would recommend switching from months to days and then divide by 30 (30 days/month) and then taking the integer value of that. This expression should do it

IIF(Car_Eff_Date<dateserial(year(date())-1,1,1),INT( (datediff("d",dateserial(year(date())-1,1,1), dateserial(year(date())-1,12,31)))/30),INT((datediff("d",Car_Eff_Date,dateserial(year (date())-1,12,31)))/30))

[jzwp11]

I would also like to calculate the number of months the contract has been active in the current year. I know this expression would be very similar, but not having too much luck as of yet.

What dates are involved. Do you have a contract start date and a contract end date?

[ErikaMarie0421]

YOU ARE AWESOME! Your expression in #8 worked perfectly.

Now to answer your question in #9 - it is the same thing, same query...only instead of counting the number of months (using the same Career Effective Date) the agent was active LAST year, I want to know how many months (until Now()) the agent has had an active contract this month. So, being August, none of these should return more than 8 - but some will return less if the career effective date is from THIS year.

I know this can't be too different from the expression you just helped me with, but I'm afraid I am now working beyond my education.

Thanks again - you're great.

[jzwp11]

So basically it sounds like you want the # of months from 1/1/10 to the present or to make it more universal from January 1st of the current year to the present

So taking my earlier expression we just need to change the dateserial() that gave of 1/1/previousyear to 1/1/currentyear, so just remove the -1 from the date serial expression dateserial(year(date())-1,1,1), and instead of 12/31/previous year we just substitute today's date with the date() function


IIf(Car_Eff_Date<DateSerial(Year(Date()),1,1),Int( (DateDiff("d",DateSerial(Year(Date()),1,1),Date()) )/30),Int((DateDiff("d",Car_Eff_Date,Date()))/30))

In my earlier response, I said this:

If the earlierdatefield's value is > laterdatefield's value then the function will return a negative number, if you would rather see zero, you can nest the datediff() function within an IIF() function.

If the Car_Eff_date occurs this year then the expression for last year's info will return a negative # of months, so we have to adjust that expression by nesting another IIF() function that tests if the value returned by the datediff() function<0

This is the amended expression (for last year's info)
IIf(Car_Eff_Date<DateSerial(Year(Date())-1,1,1),Int((DateDiff("d",DateSerial(Year(Date())-1,1,1),DateSerial(Year(Date())-1,12,31)))/30),IIF(Int((DateDiff("d",Car_Eff_Date,DateSerial( Year(Date())-1,12,31)))/30)<0,0,Int((DateDiff("d",Car_Eff_Date,DateSerial( Year(Date())-1,12,31)))/30)))

[RuralGuy]

Erika,
Are you ready to follow the link in my sig and mark this thread as Solved? I think jzwp11 did a super job here.