Welcome to SPN

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

Sign Up Now!

Time Calculation

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

  1. Jenn

    Jenn
    Expand Collapse
    Guest

    I need to show the age of something that was entered in my database. Meaning,
    I need to have the difference between the time stamp when the record was
    entered and the current date. In terms of months. I need to have this show up
    with the records on my report as a field named "age". I have a column in my
    main table that has the timestamp for when the record was entered (its called
    "date found"). The problem is I do not know how to take that date and perform
    the calc from a current date. I am not even sure where to do this
    calculation... Any help would be greatly appreciated! TIA!
     
  2. Rick B

    Rick B
    Expand Collapse
    Guest

    Just add an unbound field in your reports or forms and put the following in
    it...

    =IIf([datefound],DateDiff("m",[datefound],Date())\12 & " yrs. " &
    (DateDiff("m",[datefound],Date()) Mod 12 & " mts."),"")


    That will show something like...

    2 yrs. 6 mts.


    Or you could use...


    =IIf([datefound],DateDiff("m",[datefound],Date())

    That would just give the months.



    You can also do this in a query by adding a new column and then putting
    something like....

    Age: IIf([datefound],DateDiff("m",[datefound],Date())


    But, you don't need to store this in your table.

    --
    Rick B



    "Jenn" <Jenn@discussions.microsoft.com> wrote in message
    news:740DB40F-EAAA-4CCD-83D9-6E8447128B01@microsoft.com...
    >I need to show the age of something that was entered in my database.
    >Meaning,
    > I need to have the difference between the time stamp when the record was
    > entered and the current date. In terms of months. I need to have this show
    > up
    > with the records on my report as a field named "age". I have a column in
    > my
    > main table that has the timestamp for when the record was entered (its
    > called
    > "date found"). The problem is I do not know how to take that date and
    > perform
    > the calc from a current date. I am not even sure where to do this
    > calculation... Any help would be greatly appreciated! TIA!
     
  3. Jenn

    Jenn
    Expand Collapse
    Guest

    One more thing, I need my report to sort from longest age to shortest (Show
    report in decreasing age) how would I do this? Thanks for everything else and
    the super quick response!

    "Rick B" wrote:

    > Just add an unbound field in your reports or forms and put the following in
    > it...
    >
    > =IIf([datefound],DateDiff("m",[datefound],Date())\12 & " yrs. " &
    > (DateDiff("m",[datefound],Date()) Mod 12 & " mts."),"")
    >
    >
    > That will show something like...
    >
    > 2 yrs. 6 mts.
    >
    >
    > Or you could use...
    >
    >
    > =IIf([datefound],DateDiff("m",[datefound],Date())
    >
    > That would just give the months.
    >
    >
    >
    > You can also do this in a query by adding a new column and then putting
    > something like....
    >
    > Age: IIf([datefound],DateDiff("m",[datefound],Date())
    >
    >
    > But, you don't need to store this in your table.
    >
    > --
    > Rick B
    >
    >
    >
    > "Jenn" <Jenn@discussions.microsoft.com> wrote in message
    > news:740DB40F-EAAA-4CCD-83D9-6E8447128B01@microsoft.com...
    > >I need to show the age of something that was entered in my database.
    > >Meaning,
    > > I need to have the difference between the time stamp when the record was
    > > entered and the current date. In terms of months. I need to have this show
    > > up
    > > with the records on my report as a field named "age". I have a column in
    > > my
    > > main table that has the timestamp for when the record was entered (its
    > > called
    > > "date found"). The problem is I do not know how to take that date and
    > > perform
    > > the calc from a current date. I am not even sure where to do this
    > > calculation... Any help would be greatly appreciated! TIA!

    >
    >
    >
     
  4. Rick B

    Rick B
    Expand Collapse
    Guest

    Just sort the report by your DATEFOUND field. The one with the oldest date
    is going to have the largest age.


    --
    Rick B



    "Jenn" <Jenn@discussions.microsoft.com> wrote in message
    news:9769378D-839B-4B44-9B69-20FB5C8C4201@microsoft.com...
    > One more thing, I need my report to sort from longest age to shortest
    > (Show
    > report in decreasing age) how would I do this? Thanks for everything else
    > and
    > the super quick response!
    >
    > "Rick B" wrote:
    >
    >> Just add an unbound field in your reports or forms and put the following
    >> in
    >> it...
    >>
    >> =IIf([datefound],DateDiff("m",[datefound],Date())\12 & " yrs. " &
    >> (DateDiff("m",[datefound],Date()) Mod 12 & " mts."),"")
    >>
    >>
    >> That will show something like...
    >>
    >> 2 yrs. 6 mts.
    >>
    >>
    >> Or you could use...
    >>
    >>
    >> =IIf([datefound],DateDiff("m",[datefound],Date())
    >>
    >> That would just give the months.
    >>
    >>
    >>
    >> You can also do this in a query by adding a new column and then putting
    >> something like....
    >>
    >> Age: IIf([datefound],DateDiff("m",[datefound],Date())
    >>
    >>
    >> But, you don't need to store this in your table.
    >>
    >> --
    >> Rick B
    >>
    >>
    >>
    >> "Jenn" <Jenn@discussions.microsoft.com> wrote in message
    >> news:740DB40F-EAAA-4CCD-83D9-6E8447128B01@microsoft.com...
    >> >I need to show the age of something that was entered in my database.
    >> >Meaning,
    >> > I need to have the difference between the time stamp when the record
    >> > was
    >> > entered and the current date. In terms of months. I need to have this
    >> > show
    >> > up
    >> > with the records on my report as a field named "age". I have a column
    >> > in
    >> > my
    >> > main table that has the timestamp for when the record was entered (its
    >> > called
    >> > "date found"). The problem is I do not know how to take that date and
    >> > perform
    >> > the calc from a current date. I am not even sure where to do this
    >> > calculation... Any help would be greatly appreciated! TIA!

    >>
    >>
    >>
     
  5. Jenn

    Jenn
    Expand Collapse
    Guest

    Works great! Thanks!!!!!!

    "Rick B" wrote:

    > Just sort the report by your DATEFOUND field. The one with the oldest date
    > is going to have the largest age.
    >
    >
    > --
    > Rick B
    >
    >
    >
    > "Jenn" <Jenn@discussions.microsoft.com> wrote in message
    > news:9769378D-839B-4B44-9B69-20FB5C8C4201@microsoft.com...
    > > One more thing, I need my report to sort from longest age to shortest
    > > (Show
    > > report in decreasing age) how would I do this? Thanks for everything else
    > > and
    > > the super quick response!
    > >
    > > "Rick B" wrote:
    > >
    > >> Just add an unbound field in your reports or forms and put the following
    > >> in
    > >> it...
    > >>
    > >> =IIf([datefound],DateDiff("m",[datefound],Date())\12 & " yrs. " &
    > >> (DateDiff("m",[datefound],Date()) Mod 12 & " mts."),"")
    > >>
    > >>
    > >> That will show something like...
    > >>
    > >> 2 yrs. 6 mts.
    > >>
    > >>
    > >> Or you could use...
    > >>
    > >>
    > >> =IIf([datefound],DateDiff("m",[datefound],Date())
    > >>
    > >> That would just give the months.
    > >>
    > >>
    > >>
    > >> You can also do this in a query by adding a new column and then putting
    > >> something like....
    > >>
    > >> Age: IIf([datefound],DateDiff("m",[datefound],Date())
    > >>
    > >>
    > >> But, you don't need to store this in your table.
    > >>
    > >> --
    > >> Rick B
    > >>
    > >>
    > >>
    > >> "Jenn" <Jenn@discussions.microsoft.com> wrote in message
    > >> news:740DB40F-EAAA-4CCD-83D9-6E8447128B01@microsoft.com...
    > >> >I need to show the age of something that was entered in my database.
    > >> >Meaning,
    > >> > I need to have the difference between the time stamp when the record
    > >> > was
    > >> > entered and the current date. In terms of months. I need to have this
    > >> > show
    > >> > up
    > >> > with the records on my report as a field named "age". I have a column
    > >> > in
    > >> > my
    > >> > main table that has the timestamp for when the record was entered (its
    > >> > called
    > >> > "date found"). The problem is I do not know how to take that date and
    > >> > perform
    > >> > the calc from a current date. I am not even sure where to do this
    > >> > calculation... Any help would be greatly appreciated! TIA!
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page