Query help

[Guest]

I need to create a query to summarize the current month and prior 11 months
invoice totals. The query will be run each month. Here is a sample of the
code that I created, which is meant to give me 12 months running of invoice
totals:

For current month: M0:
Sum(IIf(MonthName(Month([Document_Date]))=MonthName(Month(Date())),[Amount],0))
For the prior month: M1:
Sum(IIf(MonthName(Month([Document_Date]))=MonthName(Month(Date())-1),[Amount],0))
etc, etc for each previous month, up to eleven months prior to the current
month.

The problem is: the code works only within the current calendar year, and I
have been unable to make it display the months that would fall in the prior
calendar year. Any suggestions would be appreciated.

 

[Guest]

This will go wrong when you say Month(Date())-6, for example, and the current
month is 4. You will get -2 which isn't a valid month!

Try using the dateadd function instead e.g. DateAdd("m", -6, date())

Regards

 

[Guest]

Much thanks for the answer!
--
Thanks. EdS

This will go wrong when you say Month(Date())-6, for example, and the current
month is 4. You will get -2 which isn't a valid month!

Try using the dateadd function instead e.g. DateAdd("m", -6, date())

Regards

I need to create a query to summarize the current month and prior 11 months
invoice totals. The query will be run each month. Here is a sample of the
code that I created, which is meant to give me 12 months running of invoice
totals:

For current month: M0:
Sum(IIf(MonthName(Month([Document_Date]))=MonthName(Month(Date())),[Amount],0))
For the prior month: M1:
Sum(IIf(MonthName(Month([Document_Date]))=MonthName(Month(Date())-1),[Amount],0))
etc, etc for each previous month, up to eleven months prior to the current
month.

The problem is: the code works only within the current calendar year, and I
have been unable to make it display the months that would fall in the prior
calendar year. Any suggestions would be appreciated.