Group on current year Quarter 1

[gdev]

Hello

I would like to group dates by the current year's quarter, excluding previous years dates.
But I need to total previous years transactions in the groups footer, if less than zero.
The following is what I am using in the group header text box control source:
IIf(DatePart("yyyy",[transactiondate])=DatePart("yyyy",Date()),DatePart("q",[transactionDate])) - doesn't work

Also I have attemtpted to use Year([TransactionDate]= Year(Now())in the group section text box control
source. For some reason, it doesn't work. What am I doing wrong.
My idea was to do all the work in a report instead of using several queries
because eventually I am going to have to group on month, 45 days, quarter and Year.
Ex.
date field2 field3 amount
1/21/2001 -1,345,579
3/27/2002 100,347
4/18/2004 17,220

 

[randysmid]

Hi,
I wrote a few FAQ's that might be able to help you.

First, here is an FAQ on how to sort by any normal business calendar, such as quarter. faq703-2980

Second, you can use DSum functions in your report footer to retrieve previous years balances. This can be used to get other special totals as well, such as "today minus 45 days". faq703-3066

HTH,
Randy Smith, MCP
California Teachers Association