Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Date calculation needed (2002)

    Hi,

    I have [start date] in a query. I need to calculate the date date 2 years and 8 months after the [start date] and am not sure where the start. The date does not have to include the day, just the month and year. So if the date is 6/2/2007 I need the query to return March 2010. It could be the first day of the month.

    Thanks!
    Leesha

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation needed (2002)

    You could create your own function to do this:

    Function plus20(stDate As Date) As Date
    Dim FirstOfMonth As Date

    FirstOfMonth = stDate - Day(stDate) + 1

    plus20 = DateAdd("m", 20, FirstOfMonth)

    End Function

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation needed (2002)

    Shouldn't you be adding 32 rather than 20 months to the date?

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation needed (2002)

    Only if you want to use a year with 12 months instead of 6. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: Date calculation needed (2002)

    Edited by HansV - thanks to JSwedlund for pointing out the error in the original version!

    You can also calculate it without a function:

    d: DateAdd("m", 32, [Start Date]-Day([Start Date])+1)

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation needed (2002)

    Sorry, I guess I just like to nitpick. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date calculation needed (2002)

    Thanks Hans! Worked perfectly.

Posting Permissions

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