Hello,

I'm working with data that includes 6 months of results (counting the current month). Using DateAdd I've been able to get the data to go back to the first of the month so it captures the entire data regardless of the current date. Hopefully that makes sense. So for example as of today it's pulling in 9/1/2013 - today. So now I need to capture (from this data) 2 quarters, the current quarter and the previous quarter. My logic is I want it to include the full three months of the previous quarter (so it would ignore the September data because I don't have the full quarter represented) and the current quarter no matter how much of it I have. It's a running data sort of thing. Can this be done? I've tried to DateAdd using "q" but it's not liking my logic. Any assistance would be great. Hopefully you can understand what I'm after.

Thanks in advance!

Sarah

(DateDiff("m",[Dt],Date())

You can use function and it is right way if you don't want to use web database. You can trust Gustav's solution. He is the best in date functions.

I can explain my idea:

1. Calculate month's number in current quarter:

Month(Date()) Mod 3

remainder after number is divided by 3

result will be 1 for January, April, 2 for February, May, but 0 for March and June

But we need values 1, 2, 3, so we can use Choose function for correct values: 1, 2, 3

Index in this function should start from 1, so we should add 1 to Mod's result:

Choose(Month(Date()) Mod 3+1,3,1,2)

You like to have all previous quarter, so you should add 3:

Now we can go back with DateDiff and get result

It can also be done with function:

Open in new window

Database2-1.accdb