Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculated Dates

Discussion in 'Information Technology' started by Connie, Jul 28, 2006.

  1. Connie

    Connie
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    India Reading Calculated Banis Breaking News Mar 6, 2013
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012
    India Seven SAD Candidates of SGPC Win Unopposed Breaking News Aug 27, 2011

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    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" wrote:

    > 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
     
  4. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Connie" <Connie@discussions.microsoft.com> wrote in message
    news:B70A9739-69EE-41F9-A5C3-2B2D49C4E983@microsoft.com
    > 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.


    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)
     
  5. Connie

    Connie
    Expand Collapse
    Guest

    Thank you! It worked like a charm.
    --
    ~Connie


    "Dirk Goldgar" wrote:

    > "Connie" <Connie@discussions.microsoft.com> wrote in message
    > news:B70A9739-69EE-41F9-A5C3-2B2D49C4E983@microsoft.com
    > > 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.

    >
    > 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)
    >
    >
    >
     

Share This Page