Solved

# Display quarterly data - "rolling" quarter

Posted on 2006-04-26
3,597 Views
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.

Thanks!
0
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()
0

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?

Thanks.
0

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

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

Author Comment

Thanks for your help!
0

## Join & Write a Comment Already a member? Login.

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.

#### Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!