Calculated Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a database that needs to calculate how many days a record is
open. I have an Open Date and Closed Date field and need to calculate how
many days the record was open between the two. But I also need to calculate
the number of days a record is still open if the Closed Date is Null.
 
Connie:

You can either use the dateDiff function to return the difference in days.
To account for Nulls use the Nz function to return the current date:

DateDiff("d", [Open Date],Nz([Closed Date],Date()))

or you can use simple subtraction:

Nz([Closed Date],Date()) – [Open Date]

Ken Sheridan
Stafford, England
 
Connie said:
I have created a database that needs to calculate how many days a
record is open. I have an Open Date and Closed Date field and need
to calculate how many days the record was open between the two. But
I also need to calculate the number of days a record is still open if
the Closed Date is Null.
Click to expand...

You'll probably need an expression along the lines of

=DateDiff("d", [OpenDate], Nz([ClosedDate], Date())
 
Thank you! It worked like a charm.
--
~Connie


Dirk Goldgar said:
Connie said:
I have created a database that needs to calculate how many days a
record is open. I have an Open Date and Closed Date field and need
to calculate how many days the record was open between the two. But
I also need to calculate the number of days a record is still open if
the Closed Date is Null.
Click to expand...

You'll probably need an expression along the lines of

=DateDiff("d", [OpenDate], Nz([ClosedDate], Date())

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top