Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Answered: Need help to create a date that is more than 30 days from another date

    Hello,

    How can I create a formula in an Access (2010) query grid to see dates in field 1 that is more than 30 days from field 2?

    Field 1 = Start date
    Field 2 = Install date

    Ex: Start date>30 days from Install date

    Appreciate your help. :-)

  2. Best Answer
    Posted by VLOOKUP

    "
    Code:
    Difference: DateDiff("d",[Start Date] + 30 ,[Install Date])
    Does this work?"


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,469
    Provided Answers: 10
    have you look at DateAdd
    DateAdd('d',2,[FeildNameDate]) AS D1
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #3
    Join Date
    Sep 2008
    Posts
    150
    Quote Originally Posted by myle View Post
    have you look at DateAdd
    DateAdd('d',2,[FeildNameDate]) AS D1
    Hi Myle,

    I tried the DateAdd, but I don't think that is what I need. I tested with this formula.

    30 Day Test: DateAdd('d',30,[tbl_Date.StartDate])
    This formula adds 30 days to Start Date.

    I need to get the number of days over 30 days from Install Date.

    So, if Start Date is 3/1/15 and Install date is 4/15/15, how can I output the number of days past 30 days, which would be 16 days over. I would like to output the 16 days in the 30 Day Test column. :-)

  5. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The DateDiff() function would return the number of days between the 2 fields.
    Paul

  6. #5
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    Code:
    Difference: DateDiff("d",[Start Date] + 30 ,[Install Date])
    Does this work?
    Last edited by VLOOKUP; 04-22-15 at 11:25.

  7. #6
    Join Date
    Sep 2008
    Posts
    150
    Quote Originally Posted by VLOOKUP View Post
    Code:
    Difference: DateDiff("d",[Start Date] + 30 ,[Install Date])
    Does this work?

    YES! It worked! Thank you so much.

    Do you know how I can get rid of the "-" numbers and just convert to whole numbers? For example, I would like to convert -17 to just 17.

  8. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Check out the Int() and Fix() functions.
    Paul

  9. #8
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    Code:
    Difference: Abs(DateDiff("d",[Start Date]+30,[Install Date]))
    That should take of the negative values.

    You are welcome BTW

    Just looking to get better at Access myself!

    HTH

  10. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Brain cramp; meant the Abs() function, was thinking about another thread.
    Paul

  11. #10
    Join Date
    Sep 2008
    Posts
    150
    Quote Originally Posted by VLOOKUP View Post
    Code:
    Difference: Abs(DateDiff("d",[Start Date]+30,[Install Date]))
    That should take of the negative values.

    You are welcome BTW

    Just looking to get better at Access myself!

    HTH
    Thank you HTH and pbaldy for your recommendations and samples. I will try the "Abs(DateDiff("d",[Start Date]+30,[Install Date]))" function when I get back to work. Again, I appreciate all of your help. :-)

  12. #11
    Join Date
    Sep 2008
    Posts
    150
    Woo hoo! The Abs(DateDiff WORKED GREAT!

    Thanks for helping with my stress level. :-D

Posting Permissions

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