Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    24

    Unanswered: Query based on time until a certain date

    Hi all,

    Basically I have a database with Risk Assessments (Yes, H&S, I know...). Each assessment has a date when it was reviewed.

    Our latest H&S audit has declared that the Assessments need to be reviewed on a yearly basis.

    What I'm looking for is a query to find the assessments that are due for review in LESS THAN A MONTH (from today)

    Once I've got this I'm going to use a macro to get it into a report, then e-mail it out. Then use Windows Task Scheduler to automate it to run once a month.

    Any help on how the date section of the query needs to be written would be greatly appreciated.

    /David

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With LCF_Data being the name of a table, this query retrieves rows where the contents of Date_Review (a Date/Time column in LCF_Data) is in the future but less than one month from today:
    Code:
    SELECT LCF_Data.*
    FROM LCF_Data
    WHERE LCF_Data.Date_Review >Date() And LCF_Data.Date_Review < DateAdd("m",1,Date());
    If you want all records, even if Date_Review is in the past, it becomes:
    Code:
    SELECT LCF_Data.*
    FROM LCF_Data
    WHERE LCF_Data.Date_Review < DateAdd("m",1,Date());
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Posts
    24
    Nice one! Cheers mate.

    I'm assuming that changing 'm' for 'w' would substitute months for weeks

    /David

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    "w" is for the day of the week. To specify weeks as the interval use "ww".

    You're welcome by the way
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •