Results 1 to 15 of 15
  1. #1
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44

    Create a TTM (trailing twelve month) query

    Hello all,

    I'm trying to create a query that will give me all of the records for the last 12 months, but I want to exclude the current month from the results. I'm currently using the DateSerial Fx in the criteria. It's currently November 2023.

    When I use
    >=DateSerial(Year(Date())-1,Month(Date()),1)

    I get the records from November 2022 - November 2023

    When I use
    >=DateSerial(Year(Date())-1,Month(Date())-1,1) (red to indicate the only difference btwn the two functions)



    I get the records from October 2022 - November 2023

    How can I exclude November 2023 (the current month) from the results?

    Thanks in advance!

  2. #2
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Maybe like this?
    Code:
    YourDate >= DateSerial(Year(Date())-1,Month(Date()),1) AND YourDate < DateSerial(Year(Date()),Month(Date()),1)
    Groeten,

    Peter

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,773
    Use DateSerial(Year(Date()),Month(Date()),0) as the last criteria?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    an alternative method

    >dateadd("yyyy",-1,date()-day(date)) and <=dateadd("d",-day(date),date)

  5. #5
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    Quote Originally Posted by xps35 View Post
    Maybe like this?
    Code:
    YourDate >= DateSerial(Year(Date())-1,Month(Date()),1) AND YourDate < DateSerial(Year(Date()),Month(Date()),1)

    Thanks Peter! Your suggestion didn't include the records from last October. However, adding "-1" after the first month parameter produces the desired result!

    >=DateSerial(Year(Date())-1,Month(Date())-1,1) And <DateSerial(Year(Date()),Month(Date()),1)

    Interestingly, I had tried some similar variations with "AND" but I must have missed this one , duh...

  6. #6
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    Quote Originally Posted by Welshgasman View Post
    Use DateSerial(Year(Date()),Month(Date()),0) as the last criteria?
    Hey Welshgasman, I tested your suggestion with "0" in the last (Day) argument for DateSerial, but the result excluded any of the records that occurred on the last day of the month, last year, October 2022.

    Now, if I change the "<" to "<=" in front of your code, it produces the desired result:

    >=DateSerial(Year(Date())-1,Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),0)

    So, thank you for your suggestion, too!

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,773
    Sorry, I missed that. I was concentrating on getting the last day of the previous month.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    no worries, I appreciate your input

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    I tend to avoid dateserial as it involves more function calls - 6 function calls instead of 4 in this case - plus more typing

  10. #10
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    Yeah, I was wondering about that. I'm going to give your suggestion a try later tonight or tomorrow sometime. Thanks!

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    actually, this only needs 3 functions

    >=dateadd("yyyy",-1,date()-day(date-1)) and <(date-day(date)+1)

    I've also modified to allow for leap years (affects the first part) and in case your field contains a time element (affects the second part)



  12. #12
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    I'm not using time elements with this dataset. Are you referencing the Day & Date Fx's in your statement or should I be referencing the field from the table (DateClosed). As written, it produces the following error, and I'm stumped:

    Click image for larger version. 

Name:	DateAddError.png 
Views:	11 
Size:	6.7 KB 
ID:	51114

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    you will need to show your code

  14. #14
    easyrider is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Dec 2021
    Location
    Lancaster County, PA
    Posts
    44
    it's the same thing that you posted yesterday.

    >=dateadd("yyyy",-1,date()-day(date-1)) and <(date-day(date)+1)

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,354
    it's the same thing that you posted yesterday.
    not quite. I change the second part from

    <=dateadd("d",-day(date),date)

    to
    <(date-day(date)+1)

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

Similar Threads

  1. Replies: 5
    Last Post: 04-07-2022, 12:31 PM
  2. Replies: 1
    Last Post: 02-18-2015, 08:28 AM
  3. Replies: 3
    Last Post: 12-29-2014, 10:14 AM
  4. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  5. Month and Day only not Year to create query
    By ssalem in forum Queries
    Replies: 3
    Last Post: 02-28-2013, 02:37 PM

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