# Thread: 2010 - How to subtract a date from 'today' in calculated field?

1. Registered User
Join Date
Aug 2012
Posts
3

## 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.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

3. Registered User
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
•