Results 1 to 10 of 10

Thread: Date

  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: Date

    One of the query is to look up all the jobs that have been more than one year old from todays date.

    I need help on how to set the formula for finding all da jobs that are one year old from todays date

    thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    you can do it the quick & easy way:
    Code:
    SELECT * FROM tblYourMomma WHERE fldDate < (Date() - 365)

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Or

    Code:
    SELECT * FROM YourTable WHERE Datediff("yyyy",fldDate,Date()) > 1
    Last edited by DavidCoutts; 11-09-04 at 09:33.

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    If you put this in the selection criteria for the Job Date field in your query:
    '<DateValue(Month(Now()) & "/" & Day(Now()) & "/" & (Year(Now())-1))'
    You could do it shorter by using <Now()-365, but leap years will throw you off.

    TD

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DavidCoutts
    Or

    Code:
    SELECT * FROM YourTable WHERE Datediff("yyyy",fldDate,Date()) > 1
    Ahhh ... David. This gives all the jobs more than a year old AND jobs even older than that ... Just missing the upper bound ...

    ... ((Datediff("yyyy",fldDate,Date()) > 1) AND (Datediff("yyyy",fldDate,Date()) < 2))
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    From the original Statement

    Quote Originally Posted by tanbir 17
    One of the query is to look up all the jobs that have been more than one year old from todays date.
    which is why i didnt set the upper bound
    the second line asks for a year old maybe i should read these things more carefully

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DavidCoutts
    From the original Statement


    which is why i didnt set the upper bound
    the second line asks for a year old maybe i should read these things more carefully
    And I need to stop reading more into vague requests ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    anyway between the 2 of us tanbir 17 will havethe answer now

  9. #9
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    The problem with the DateDiff solutions provided here is that they don't provide what was asked for. If tanbir_17 has a job date of 11-01-03, that job is over a year old but the solutions using "((Datediff("yyyy",fldDate,Date()) > 1)..." will not pick it up because 2004 - 2003 is not greater than 1.

    As for the last sentence in the original post, if you just want the jobs that are a year old today (I thought tanbir_17 wanted what was in the first sentence and just wrote it wrong when he repeated it in the second sentence), you just drop the less than sign from my original post.

  10. #10
    Join Date
    Oct 2003
    Posts
    357

    Thumbs up

    Hi, The following also gives the required result
    Code:
    SELECT * from tablename where date()-datefield>365
    Madhivanan

Posting Permissions

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