Quarterly DateAdd function accuracy

Question

Hello. I am trending quarterly data using standard US calendar quarters, and I have built a form to produce start and end dates for the last twenty quarters that I will use for reporting on various quarterly trends, displaying the start-end date ranges.

When I use the DateAdd =DateAdd("q",-1,[end_date]) to get the previous quarter's end date, it produces dates that are not the last dates in the previous quarter in some cases (Q1 date =3-30-13 when applied to Q2 end date 6-30-13, for example).

 I've read almost everything I can find through Access help results, and the work around of using date part and last days is fine but complicated and not too clean code-wise. 

 I am looking for accurate results, but also curious as to the actual Access functionality. ie., does "DateAdd "q" just add (subrtact) 90 days to get results and why does using this method fail when quarters end in June or September (30 day months)?

Any help is appreciated.

Answer 1

Subtracting a quarter seems to be the same as subtracting 3 months.

You can use an expression like:

=DateSerial(Year(DateAdd("q",-1,[end_date])),Month(DateAdd("q",-1,[end_date]))+1,0)

---

Answer 2

Thank you, that solution works perfectly and will help tremendously with rolling quarters as well. 

I also will try this same logic for the "Rolling 12 month" part of the report that I am building.  I appreciate the quick and helpful repsonse.

It's also good to know that adding (subtracting) a quarter appears to be equivalent to adding three months so the expression needs to be a bit more robust that a simple "DateAdd".