Last full 3 full months criteria formula

[FrostByte]

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]

Hi. Have you tried something like?

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

 

[arnelgp]

where [datefield] >= dateadd("m", -3, date)

 

[June7]

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

 

[Pat Hartman]

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]

I tested with -3 and it works. The result is

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

 

[isladogs]

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

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]

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

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]

LOL. Its only because I tested yours and Pat's code that I noticed.

 

[theDBguy]

LOL. Its only because I tested yours and Pat's code that I noticed.

Well, there you go. That's another thing I don't do - Testing.

 

[Pat Hartman]

I guess both theDBguy and I were BOTH having senior moments
Thanks

 

[theDBguy]

I guess both theDBguy and I were BOTH having senior moments
Thanks

And lately, it's been happening more than I'd like to admit.

 

[FrostByte]

Guys, this is brilliant and works perfectly.

I cant thank you enough.


Kind Regards
Steve

 

[theDBguy]

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.