Group by Current Year Quarter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gwen - 15 Nov 2004 19:58 GMT

I would like to group my report on current year's transactions only. But I
do want in the group's footer I would like to have the previous years total.
I have used the below iif statement in the transactiondate text box control
source.

=IIf(DatePart("yyyy",[transactiondate])=DatePart("yyyy",Date()),[transactiondate])
The report still display previous years transactions.
Please assist.

Thanks a bunch
Gwen

Reply to this Message

Fons Ponsioen - 16 Nov 2004 16:44 GMT

Hi Gwen.
Do I understand correctly, you want the detail of the report to show the
current year detail data, and in the group footer you would like to show the
previous year total.
You did not indicate what you are grouping by.
The way I would approach this is to create a report and filter the data to
the current year by using:
Year([TransactionDate]= Year(Now())
or you can just put a statement like >= #1/1/2004#.
Than I would create a second totals query limiting the data to the previous
year including the client ID so you would have a query with the client ID and
the previous year total.
If you now include this totals query in the source query for your report
above linking by client ID now you have the data from the previous year
available for your report.
Hope this helps.
Fons

> I would like to group my report on current year's transactions only. But I
> do want in the group's footer I would like to have the previous years total.
[quoted text clipped - 7 lines]
> Thanks a bunch
> Gwen

Reply to this Message

Gwen - 16 Nov 2004 21:05 GMT

Hi Fons,
Thank you, thank you for responding.
I am grouping by quarter. 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.

Thanks again
Gwen

> Hi Gwen.
> Do I understand correctly, you want the detail of the report to show the
[quoted text clipped - 25 lines]
> > Thanks a bunch
> > Gwen

Reply to this Message

Fons Ponsioen - 17 Nov 2004 13:34 GMT

Gwen, I am not sure without further detail.
If you wish you could send me sample mdb file with some data, and I would
gladly take a look at it.
my IP is fonsponsio at sbcglobal.net
Fons

> Hi Fons,
> Thank you, thank you for responding.
[quoted text clipped - 37 lines]
> > > Thanks a bunch
> > > Gwen

Reply to this Message