Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to find whole months only (Access 2k Win 2k)

    Hello again

    I'm trying to work out how to count the total number of whole months between any two dates. Is there an easy way of doing this, or will it need some serious SQL / VBA?

    Thanks

    ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How to find whole months only (Access 2k Win 2k)

    The DateDiff() function can be set to calculate the difference in months between two dates, but I'm not sure what you mean by whole months between two dates. For example if date 1 is March 1 and date 2 is June 30 is the answer 4? And if date 1 is March 2 and date two is June 29 is the answer 2? I haven't tested DateDiff, but I think it may give the same answer in both cases.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to find whole months only (Access 2k Win 2k)

    If you have two dates Date1 and Date2, the number of whole months between them is

    DateDiff("m", Date1, Date2) + (Day(Date1) > Day(Date2))

    DateDiff just takes the months of Date1 and Date2 into account, it doesn't look at the day in the month of either date. (Day(Date1) > Day(Date2)) is either True (-1) if the first date is on a later day in the month than the second date, or False (0). We use this to subtract 1 from the number of months if the first date is on a later day in the month than the second date.

    If you need this often, you could create a custom VBA function for this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •