Results 1 to 7 of 7
  1. #1
    jsommer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    4

    Filter for last 365 days from given date

    Hello everyone,

    I have a query that I run once a month to show any records from the past 12months/year/365days from a given date. Currently I'm using this criteria: >=#1/1/2015# And <=#12/31/2015#.
    Ideally, I'd like to be able to specify/enter the date and have it use that to show any records from the last year (365 days) from that date.

    does that make sense? If entering a date makes it too difficult, is my current criteria my best option?




    Thank you for all of the help!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this as criteria

    BETWEEN YourDate AND YourDate - 365

    You may need a Parameters query to avoid having to enter yourDate twice.
    Here is typical SQL:
    Code:
    PARAMETERS [YourDate] DateTime;
    SELECT your fields here
    FROM yourTable here
    WHERE yourfield Between [YourDate] And [YourDate] - 365;

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Orange,

    I believe you have a typo in your WHERE clause. You do not want the 365 inside the square brackets, else it is part of the field name.
    Code:
    WHERE yourfield Between [YourDate] And [YourDate]-365;

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks Joe -- you're correct.
    I'll adjust it.

  5. #5
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    You actually asked two different questions.

    1) From the date and including the last 365 days
    2) From the date and including the last year.

    Leap years cause problems sometimes when you use a day count modifier. If that may be an issue for your needs, you may want to use:

    Code:
    [yourField] between [StartDate] and dateadd(-1,"yyyy",[StartDate])
    Cheers,


    Jeff

  6. #6
    jsommer is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2014
    Posts
    4
    Clarification: if I copy/paste the suggested criteria, it asks me to enter "your date"... which is what I was thinking. however, when I do that I get an error.

    I know I can replace [yourdate] with the #actualdate# but I just want to make sure if that's what is implied.

    thanks for all the help.

  7. #7
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by jsommer View Post
    Clarification: if I copy/paste the suggested criteria, it asks me to enter "your date"... which is what I was thinking. however, when I do that I get an error.

    I know I can replace [yourdate] with the #actualdate# but I just want to make sure if that's what is implied.

    thanks for all the help.
    Predefine [yourdate] as a parameter with a Datetime type.

    Jeff

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-31-2013, 08:19 AM
  2. Replies: 1
    Last Post: 11-26-2013, 09:25 AM
  3. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  4. Replies: 7
    Last Post: 01-28-2013, 05:21 PM
  5. Replies: 2
    Last Post: 07-31-2009, 06:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums