Create a TTM (trailing twelve month) query

[easyrider]

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!

[xps35]

Maybe like this?

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

[Welshgasman]

Use DateSerial(Year(Date()),Month(Date()),0) as the last criteria?

[CJ_London]

an alternative method

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

[easyrider]

Maybe like this?

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...

[easyrider]

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!

[Welshgasman]

Sorry, I missed that. I was concentrating on getting the last day of the previous month.

[easyrider]

no worries, I appreciate your input

[CJ_London]

I tend to avoid dateserial as it involves more function calls - 6 function calls instead of 4 in this case - plus more typing

[easyrider]

Yeah, I was wondering about that. I'm going to give your suggestion a try later tonight or tomorrow sometime. Thanks!

[CJ_London]

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)

[easyrider]

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:

[CJ_London]

you will need to show your code

[easyrider]

it's the same thing that you posted yesterday.

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

[CJ_London]

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)