Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Question Unanswered: 2010 - How to subtract a date from 'today' in calculated field?

    This should be easy, but I'm struggling because it's been years since I used Access. I want to build a calculated field in a table that is conditional. If the value in one field [Approval Date] is empty, I want to subtract [Engage Date] from the current date. If the value in [Approval Date] is not empty, I want to subtract [Engage Date] from [Approval Date].

    In other words, I am trying to track the total number of days a task has been in process. If there is an Approval Date, I can subtract the Engage Date and that tells me the length (in days) of the task. However, if there is no Approval Date, I still want to know how many days the task has been open. That's why I'm thinking a good way to go about it is with a conditional calculation.

    Joined the forum today, and this is my first Post - so be gentle if I messed up the question.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    short answer don't create a value in the table, calculate it as and when you need it
    I'd suggest you write a function which returns the required date in a code module. Then call that function from a query, form or report as required

    Code:
    Public Function GetNoDays(date1 As Variant, date2 As Variant) As Integer
    'see http://www.dbforums.com/microsoft-access/1684648-2010-how-subtract-date-today-calculated-field.html#post6566201
    'parameters
    'date1
    'date2
    'what this does
    'if date1 is not set then we subtract the current date from date2
    If IsNull(date1) Then
      If IsNull(date2) Then ' we have null dates for both, very very naughty user
        'so we will return a NULL just becuase we can
        GetNoDays = 0
      Else 'find the number of days difference between the current date and date2
        GetNoDays = DateDiff("d", Date, CDate(date2))
      End If
    Else
      If IsNull(date2) Then
        GetNoDays = 0 'if they think they can sneak duff data in on date 2 they can....
      Else 'subtract the value of date2 from date1
        GetNoDays = DateDiff("d", CDate(date1), CDate(date2))
      End If
    End If
    GetNoDays = Abs(GetNoDays) 'mak esure the value is alwasy positive in case there's some funny stuff going on round here
    End Function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    3
    Thanks for the suggestion. This solution will definitely test the limits of my ability. However, I can see how (once I do just a bit of reading to study up) it is probably a better overall method in the long run. Just goes to show how much I have to learn.

Posting Permissions

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