Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Date Query

  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Date Query

    I am creating a date criertia on a query for a report that only shows WillDate of up to 5 years ago to the current month as well as Codicil Date 5 years ago.

    I believe the way to check the 5 years on the query criteria would be the following;
    Code:
    =DateAdd("yyyy",-5,[Codicil Date])
    But how would i check the Will Date to be 5 years ago to the current month.
    So I can view all the records for 5 years ago to say June.

    Any suggestions for the query criteria for willdate?

    I have tried DatePart and DateAdd but can't seem to get the coding correct!

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    DateDiff , did you try that? Are you trying to show ALL records for will from June - 5yrs? or just all records of June 5 years ago?

    Either way, I think the DateDiff will work.

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    thanks. i will try that.
    i am trying to show records of 5 years ago for june.
    so all June 2002 only!

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Yeah, DateDiff should work. Syntax for DateDiff is like:


    http://www.csidata.com/custserv/onli...docs/vbs95.htm



    EDIT::::

    This one is a little better / easier (for me at least)

    http://www.techonthenet.com/access/f...e/datediff.php

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    having problems with the syntax for the datediff within the query linked to the date.
    Any help would be appreciated, as I have gone round in circles with the code.

    the code i have on my query criteria for willdate is as follows;
    DateDiff("y",Now(),[Will Date]-5)

    By my records it should be based on the year from Today's date and calculate 5 years back on Will Date.
    So as of today it should show all the Will Date Records from 5 Years ago

    This was my test, as I want it to work out all the records as of today for 5 years ago to this month (eg: 5 Years ago with June as the month)

    UPDATE:
    Code:
    <=DateAdd("yyyy",-5,[Will Date]) And Year([Will Date])*12+DatePart("m",[Will Date])-1
    By my estimations this should do what I want it to but is not?
    Last edited by NeilMansell; 06-15-07 at 07:55.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by NielMansell
    By my records it should be based on the year from Today's date and calculate 5 years back on Will Date.
    So as of today it should show all the Will Date Records from 5 Years ago
    You're right to move on to the DateAdd function, that's for sure!
    The following will give you things where the will date is exactly 5 years ago today.
    Code:
    WHERE [Will Date] = DateAdd(yy, -5, Date())
    If, however, you want to return all the results where the will date is in this month 5 years ago then you might want to try this.
    Code:
    WHERE Month([Will Date]) = Month(DateAdd(yy, -5, Date())
    AND Year([Will Date]) = Year(DateAdd(yy, -5, Date())
    Please note that none of this is tested
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    thanks but im still having the same problems with invalid procedure and all that!!!
    Also why would you want the 'WHERE Month([Will Date]) = Month(' part in the code? just so i understand.

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    even the
    WHERE [Will Date] = DateAdd(yy, -5, Date())
    comes back with an error?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you building this is the query editor?
    I can never remember if it is Date() or Now() that you need in access... Try them both
    other things that vary corss platform is the need to surround the datepart (in this case yy) with quotes. Try double and singles. If that fails resort to four y's instead of 2 (yyyy).

    Finally - what is the error?
    George
    Home | Blog

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    yes in the query editor...

    this doesn't error:

    Code:
    <=DateAdd("yyyy",-5,Now())
    It appears it gives me results for all years from 5 years onwards, where my next step is to grab years of 5, 10, 15, 20, 25, 30 in the same query.
    So in this example 2002, 1997, 1992, 1987, 1982 and 1977 only

    But that still doesn't work out the current month, so June and years;
    2002, 1997, 1992, 1987, 1982 and 1977

    Get my drift?!

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    UPDATE:

    =DateAdd("yyyy",-5,Now())

    This does not return years 2002???? It displays nothing.

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Been on hol for a few days so bit late with this contribution, but I find/believe that DateSerial() is the most powerfull/flexible of the date functions and suggest this for June 5 year prior the [Codicil Date] ie

    = DateSerial(Year([Codicil Date])-5, 6, Day([Codicil Date]))

    This gives you the same day in june 5 years before the YEAR of [Codicil Date]

    ie if [Codicil Date]= 10 july 2007
    then the 10 June 2002 is returned.

    Does that help?

    MTB

    ps I think WHERE [Will Date] = DateAdd(yy, -5, Date())

    should be WHERE [Will Date] = DateAdd("yyyy", -5, Date())

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's because it's returning the value of 15-Jun-2002.
    Quote Originally Posted by georgev
    If, however, you want to return all the results where the will date is in this month 5 years ago then you might want to try this.
    Code:
    WHERE Month([Will Date]) = Month(DateAdd(yy, -5, Date())
    AND Year([Will Date]) = Year(DateAdd(yy, -5, Date())
    Let's concentrate on getting 1 set of 5 years ago first.
    George
    Home | Blog

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    ok, but what i don't understand is do i need to do this on SQL code of the query as when I do it creates a new field named MONTH on the query design?
    Is this normal?

    How would I create it in the design area not the SQL area?

  15. #15
    Join Date
    Mar 2004
    Posts
    287
    Code:
    WHERE Month([Will Date])=Month(DateAdd("yy",-5,Date()) And Year([Will Date])=Year(DateAdd("yy",-5,Date())));
    Added to the SQL Part at end gives an 'Invalid procedure call' error.

Posting Permissions

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