Results 1 to 8 of 8
  1. #1
    yyhoop is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    6

    Days remaining in a report

    I'm trying to subtract 2 dates and return a number within a report




    Basically days remaining between a date in the future and current date.


    This is what I want as a result...I want to take a given date in the future say 28 August 2017 and have it give a result of the days remaining based on the current date. I have tried expressions in a table, a query, within the report and the best I have done is get the result to show as a date instead of a number ie....[DaysRemaining] - 3 among others, tried to format as an integer among others and none seem to work.

    Any help is much appreciated

    Hoop

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dateDiff("d",[date1], [date2])

  3. #3
    yyhoop is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    6
    So if date 2 is the target date in the future, which is hard entry on the form, what do I use for date 1,

    I tried......dateDiff("d",[targetdate], [Now])

    It does the calculation once you enter a date parameter in the prompt window for the value of.... Now

  4. #4
    yyhoop is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    6
    Correction

    So if date 1 is the target date in the future, which is hard entry on the form, what do I use for date 2,

    I tried......dateDiff("d",[targetdate], [Now])

    It does the calculation once you enter a date parameter in the prompt window for the value of.... Now

  5. #5
    yyhoop is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    6
    So how do you get it to pull the current date into the expression?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Date() is builtin to Access (returns today's date)

    something like
    someDiff = datediff("d",Date,FutureDate)

    datediff

  7. #7
    yyhoop is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    6
    Nothing that I did was working to get the query to pull the current date from the system as the datediff function wanted 2 date entries from the table to accomplish the calculation [date1],[date2]. Trying many different ways to manipulate the datediff function to pull current system time in to the expression I surrendered to the fact that I was just going to have to enter the current date manually each time the parameter value entry box appeared.

    Not so: A simple table line entry ID: Current Date....Format: Date/Time
    Add the Current Date to the query and the expression Now()

    datediff("d",[Current Date],[Follow Up]) did the trick

    such a simple solution...I got way off track trying to manipulate an exression instead of doing the obvious
    Attached Files Attached Files

  8. #8
    yyhoop is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    6
    Problem Solved

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automatically Display days remaining
    By AndyC121 in forum Access
    Replies: 4
    Last Post: 03-23-2017, 08:01 AM
  2. In a report, limit data to 90 days before today.
    By Ed Mattison in forum Reports
    Replies: 2
    Last Post: 12-14-2016, 03:14 PM
  3. Replies: 9
    Last Post: 03-07-2016, 04:17 PM
  4. Replies: 2
    Last Post: 04-10-2015, 03:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums