Display quarterly data - "rolling" quarter

Posted on 2006-04-26
Last Modified: 2012-08-13
User would like to see 3 months of data at a time. For example, today is 4/26/06. He would like to see all of February, March and April (to-date). When May rolls around, February will drop off and he would like to see March, April and May (to-date). Any ideas on how to accomplish this in a query? There is a field in the query called Close Date that I will use and need to add the search criteria to that.

Question by:khawkins96
    LVL 14

    Expert Comment

    For 2/1/06 to 4/26/06:

    SELECT * FROM YourTable WHERE CloseDate BETWEEN CDate(Month(Date())-2 & "/1/" & Year(Date())) AND Date()

    For 2/26/06 to 4/26/06:

    SELECT * FROM YourTable WHERE CloseDate BETWEEN DateAdd("m", -2, Date()) AND Date()

    Author Comment

    JohnK813, thank you for responding.

    4/26/06 was just the example. I need it to be able to take today's date, Now(), and find the three months from there. Also, I would prefer not to have to "hard code" this. Is there another way of doing it?

    LVL 14

    Accepted Solution

    Sorry, guess I was a little misleading.  What I was trying to say was that my first statement started at the 1st of the month two months ago, and my second statement started exactly 2 months ago.

    Neither is "hard coded," though.  Date() is esentially the same as Now().  The only difference is that Date() just returns the current date, whereas Now() also returns the time.  So, if you replace Date() with Now() you should be set.

    Some more explanation:

    CDate(Month(Date())-2 & "/1/" & Year(Date()))
    Month(Date())-2 takes the month of the current date and subtracts 2
    Year(Date()) gets the year of the current date
    CDate converts the month + /1/ (the day) + the year into date format

    DateAdd("m", -2, Date())
    Subtract two months (or, if you prefer, "add -2 months") to the current date.

    Author Comment

    Thanks for your help!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now