Results 1 to 12 of 12

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

  1. #1
    ErikaMarie0421 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Posts
    10

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

    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!!!

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    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.

  3. #3
    ErikaMarie0421 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Posts
    10
    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)?

  4. #4
    ErikaMarie0421 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Posts
    10
    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!!

  5. #5
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    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.

  6. #6
    ErikaMarie0421 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Posts
    10
    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?

  7. #7
    ErikaMarie0421 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Posts
    10
    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.

  8. #8
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    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))

  9. #9
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    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?

  10. #10
    ErikaMarie0421 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Posts
    10
    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.

  11. #11
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,896
    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)))

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,903
    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.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-07-2010, 03:22 PM
  2. Query criteria, add 5 months to date
    By thart21 in forum Queries
    Replies: 1
    Last Post: 03-29-2010, 04:09 PM
  3. Use field value as the year in a date
    By Eveline in forum Queries
    Replies: 1
    Last Post: 03-11-2010, 08:58 AM
  4. How to calculate number of weeks
    By FeatherDust in forum Queries
    Replies: 0
    Last Post: 09-18-2009, 01:50 PM
  5. Replies: 1
    Last Post: 12-09-2005, 08:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums