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,418
    Provided Answers: 7
    have you look at DateAdd
    DateAdd('d',2,[FeildNameDate]) AS D1
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
    268
    Provided Answers: 6
    Code:
    Difference: DateDiff("d",[Start Date] + 30 ,[Install Date])
    Does this work?
    Last edited by VLOOKUP; 04-22-15 at 12: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
    268
    Provided Answers: 6
    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
  •