Last full 3 full months criteria formula (1 Viewer)

FrostByte

Registered User.
Local time
Today, 10:24
Joined
Jan 15, 2015
Messages
56
Hi,

I'm trying to return the last 3 full months of data up to current date, so if today is 24/10 the return would be between 01/07 to 24/10

i.e the criteria will always give me the full 3 months and the current month to date.


Thanks in advance
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:24
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried something like?
Code:
Between DateSerial(Year(Date()),Month(Date())-3,1) And Date())
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:24
Joined
May 7, 2009
Messages
19,175
where [datefield] >= dateadd("m", -3, date)
 

June7

AWF VIP
Local time
Today, 02:24
Joined
Mar 9, 2014
Messages
5,425
Use theDBGuy DateSerial() version.

Your date example shows as dd/mm, if your criteria is not U.S. standard of mm/dd/yyyy, could have issues. Review http://allenbrowne.com/ser-36.html
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 19, 2002
Messages
42,981
Your example shows three full months plus month to date. Therefore you need to go back FOUR months for your starting date.

Between DateSerial(Year(Date()),Month(Date())-4,1) And Date())
 

June7

AWF VIP
Local time
Today, 02:24
Joined
Mar 9, 2014
Messages
5,425
I tested with -3 and it works. The result is

BETWEEN 7/1/2019 AND 10/24/2019
 

isladogs

MVP / VIP
Local time
Today, 10:24
Joined
Jan 14, 2017
Messages
18,186
Agree that the value should be -3 as in DBG's answer and not -4 as Pat stated.
However need to remove the final ) bracket. It should be
Code:
Between DateSerial(Year(Date()),Month(Date())-3,1) And Date()

With regard to date formats, using dd/mm/yyyy works perfectly in queries.
Its only in SQL statements that its necessary to convert dates to mm/dd/yyyy format
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:24
Joined
Oct 29, 2018
Messages
21,358
Agree that the value should be -3 as in DBG's answer and not -4 as Pat stated.
However need to remove the final ) bracket. It should be
Code:
Between DateSerial(Year(Date()),Month(Date())-3,1) And Date()[/QUOTE]
What? I didn't put that there! Just kidding... Thanks, Colin. Good eye!
 

isladogs

MVP / VIP
Local time
Today, 10:24
Joined
Jan 14, 2017
Messages
18,186
LOL. Its only because I tested yours and Pat's code that I noticed.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 19, 2002
Messages
42,981
I guess both theDBguy and I were BOTH having senior moments:confused: :confused:
Thanks
 

FrostByte

Registered User.
Local time
Today, 10:24
Joined
Jan 15, 2015
Messages
56
Guys, this is brilliant and works perfectly.

I cant thank you enough.


Kind Regards
Steve
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:24
Joined
Oct 29, 2018
Messages
21,358
Guys, this is brilliant and works perfectly.

I cant thank you enough.


Kind Regards
Steve

Hi Steve. Glad to hear you got it to work. We were all happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom