Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2007
    Posts
    38

    Unanswered: running a query on dates

    Ok, it should be simple, but its not doing what i want.

    I am trying to set up queries to pick up dates that are over 3 years old (done this via <now()-1095
    but am trying to do a 2-3 year old query....I thought this was using the between function but it doenst seem to like it, or will this only work with specific dates rather that now() or date() functions?

    (I will also want to do a 1-2 year and 0-1 year query as well)

    was trying to do it withought using specific dates as i can then continue using the same queries.

    Many thanks for your help and patience!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Look at the DateAdd() function
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Posts
    38
    doenst realy seem to do waht I am after though

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aren't you aiming for
    Code:
    WHERE   yourDateField is less than todays date minus 3 years
    ?
    If so, DateAdd() is definately the answer
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2007
    Posts
    38
    I have looked at this again George, and it looks like this will only give new infomation in a new field (ie if you order something and want a reminder 4 days later you would use dateadd("d",14,[Dateordered]) where if dateordered was 14/02/08 it would return 28/02/08)

    doesnt seem to be able to list dates between a and b (which you can do if you use Between #01/02/08# and #01/03/08# but i dont want fixed dates, but rolling period of last 12 months, 1-2 years etc) and produce a list of the entries within that period

    apologies if i am being dozy here!

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To filter to all records for the last year
    Code:
    WHERE   someField > DateAdd(yyyy, -1, Now())
    To filter for all records that are over 1 year old but less than 2 years old
    Code:
    WHERE   someField BETWEEN DateAdd(yyyy, -1, Now())
                          AND DateAdd(yyyy, -2, Now())
    George
    Home | Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If I'm still not hitting the mark; it's because your requirements aren't clear; provide sample data and required results.
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Posts
    38
    ok,

    what i am after is a query i can run that will list all the records in my main table that has contracts that ran out between 2 and 3 years ago (3-4 years,1-2 years,0-1 years, etc)

    I could use between #01/01/05# and #01/01/06# (or similar dates) but would prefer to be able to have it so it is always 2-3 years behind rather than set dates

    Ideally this can be done in the query area rather than as VBA (not too great in the VBA area!)

    (I dont want to look at dates and create a new field with a new date in it which the dateadd function seems to do)

    hope this makes sense?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    BETWEEN DATEADD("yyyy",-3,Date()) AND DATEADD("yyyy",-2,Date())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2007
    Posts
    38
    ok, put that into my query and it pulled up all the records in the database!
    does the YYYY need to actually be entered as the year or just as YYYY?

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ I think George understood perfectly.

    Your answer is right before you.

    DateAdd()

    It doesn't create new fields or anything, it RETURNS dates based on calculations. It is used to say things like "two weeks ago", "A year from now" or "90 days ago".

    If you use it in the context of creating calculated controls, then you'll get a new date field. If you use it in a criteria row, then it acts as a date criteria, if you use it in a messagebox expression, it will throw the date at the user... etc.

    This is the same for all functions... they simply return a value, that's it.

    Between DateAdd("yyyy",-3,Date()) And DateAdd("yyyy",-2,Date())
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bah, I was too slow
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wartroll
    ok, put that into my query and it pulled up all the records in the database!
    does the YYYY need to actually be entered as the year or just as YYYY?
    please show exactly what you ran

    "yyyy" is a special code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2007
    Posts
    38
    to wartroll:

    MY APOLOGIES

    i tried to edit my own reply and accidentally edited yours

    sorry

    you may wish to repost if you are still having trouble

    - r937
    Last edited by r937; 05-09-08 at 11:33.

  15. #15
    Join Date
    Nov 2007
    Posts
    38
    Quote Originally Posted by StarTrekker
    ^^ I think George understood perfectly.

    Your answer is right before you.

    DateAdd()

    It doesn't create new fields or anything, it RETURNS dates based on calculations. It is used to say things like "two weeks ago", "A year from now" or "90 days ago".

    If you use it in the context of creating calculated controls, then you'll get a new date field. If you use it in a criteria row, then it acts as a date criteria, if you use it in a messagebox expression, it will throw the date at the user... etc.

    This is the same for all functions... they simply return a value, that's it.

    Between DateAdd("yyyy",-3,Date()) And DateAdd("yyyy",-2,Date())
    I was looking at the access help screen when i replied on this, and that shows it in the context of a control, probably why i was sounding confused

Posting Permissions

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