Problem with 12 month running total on query 1

[kentwoodjean]

I am using the following criteria to obtain a 12 month running total on a query;

Between (DateSerial((Year(Now())-1),Month(Now()),1)) And (DateSerial((Year(Now())),IIf(Month(Now())=1,12,Month(Now())-0),1))

Only problem is that my August data has not arrived and normally does not come until about the 15th of the following month. Therefore, there is no entry for Aug when the query is run, and when I run my crosstab report, I get an Error because the field "Aug" has no data.

What can I do in a case like this so my report will carry Aug 2004 data until my Aug 2005 data arives on the 15th??

Thanks.

 

[THWatson]

Kent
I am assuming that this happens not only with August but with each month - in that the data for a given month does not arrive until the 15th of the following month?

If that is the case, then can you add a further IIf expression to test for >= to the 15th day, so if Now is prior to the 15th, you get the data from the previous month of the prior year...but if Now is the 15th or greater, you get data from the month of this year.

Tom

 

[kentwoodjean]

You are correct in that the data comes the 15th of each month for the previous month. I believe the IIF statement will take care of it. Thanks for the Tip.