Welcome to SPN

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

Sign Up Now!

Find records with date more than xx months

Discussion in 'Information Technology' started by James, Nov 18, 2005.

  1. James

    James
    Expand Collapse
    Guest

    Assume we have a table listing serial number (of a widget) and a date.

    The date is the date of service.

    How can we then, produce a query/report listing all widgets that have not
    been services within the last 12 months?

    Are we approching this wrongs? Should we have a fixed field showing date
    next service due? How can this be calculated from the data in the table?
     
  2. Loading...

    Similar Threads Forum Date
    Pathfinder Joins Sikh Philosophy Network! New SPN'ers Sep 5, 2016
    The Salmon Story, Finding Home Sikh Sikhi Sikhism Jul 29, 2016
    Sikhi Guru Nanak's Methodology Of Finding Truth Sikh Sikhi Sikhism Jan 1, 2016
    Movies Ashdoc's movie review---Finding Fanny ( English version ) Theatre, Movies & Cinema Sep 15, 2014
    Leisure Can someone pls help me find this Waheguru Simran...?? Business, Lifestyle & Leisure Aug 5, 2014

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Create a query based on that table, and add a calculated field to the query.
    Your calculation would be something like:

    MonthsSinceLastService: DateDiff("m", [LastServiceDate], Date())

    Put > 12 as the criteria under that field.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "James" <spamtrap@hotmail.com> wrote in message
    news:YN0ff.17029$6A4.16388@fe1.news.blueyonder.co.uk...
    > Assume we have a table listing serial number (of a widget) and a date.
    >
    > The date is the date of service.
    >
    > How can we then, produce a query/report listing all widgets that have not
    > been services within the last 12 months?
    >
    > Are we approching this wrongs? Should we have a fixed field showing date
    > next service due? How can this be calculated from the data in the table?
    >
    >
    >
     
  4. Ed Warren

    Ed Warren
    Expand Collapse
    Guest

    You will want to use the datadiff function

    MonthsSinceService: datediff("m",[lastservice],now())
    then select records with a MonthsSinceService >=12

    Ed Warren


    "James" <spamtrap@hotmail.com> wrote in message
    news:YN0ff.17029$6A4.16388@fe1.news.blueyonder.co.uk...
    > Assume we have a table listing serial number (of a widget) and a date.
    >
    > The date is the date of service.
    >
    > How can we then, produce a query/report listing all widgets that have not
    > been services within the last 12 months?
    >
    > Are we approching this wrongs? Should we have a fixed field showing date
    > next service due? How can this be calculated from the data in the table?
    >
    >
    >
     
  5. rico

    rico
    Expand Collapse
    Guest

    Calculated fields is the way to do. All you need is a text box or row in
    query with source something thing like:

    =[Datefield]+365

    Then you can run querys on this field, call it [nextservice] like:

    Where [Nextservice]<Date()

    This will return all widgets past thier service date.

    HTH

    Rico

    "James" wrote:

    > Assume we have a table listing serial number (of a widget) and a date.
    >
    > The date is the date of service.
    >
    > How can we then, produce a query/report listing all widgets that have not
    > been services within the last 12 months?
    >
    > Are we approching this wrongs? Should we have a fixed field showing date
    > next service due? How can this be calculated from the data in the table?
    >
    >
    >
    >
     

Share This Page