Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    London
    Posts
    73

    Unanswered: Dates working out

    Hi i have a table with a field called dates, which has dates from today’s date to 2005

    Eg
    25/10/2010
    26/10/2010
    27/10/2010

    What i would like to do is work out from today’s date how old the date above is by months. If i open it today it will give me a number of months from today’s date

    eg
    if i open today

    04/11/2010 - 25/10/2010 is 0

    if i open the same database next month it will give me a different value

    eg 04/12/2010 - 25/10/2010 = 1 month.

    I want this to go back to 2005.

    I tried doing this is a query but does not give an accurate figure because i was using days.

    eg

    iif(Dates()-[Date]<30,1,0)

    This would not be accurate as not all months have 30 days. Does anyone have a solution in vba or query on how i can work this out.

    Thank you

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try the datediff function
    ..however it only works on date values
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As Jaded Developer has suggested, using

    DateDiff("d",YourDateField,Date)

    will give you the exact number of days between today and a date in the past. But anytime you're dealing with months and/or years, in this manner, you're going to have to make compromises!

    DateDiff("d",YourDateField,Date)/30

    will give you a close approximation of the number of months, but as you've said, not all months have 30 days! Including Leap Year, months can be 28, 29, 30 or 31 days long.

    There's really no practical way doing this exactly, because there's no exact definition, in days, of what a month is.

    For exactness in something like this, you have to go to the lowest common factor, which in this case is days, or you have to settle for an approximation, as I said.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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