Group by Current Year Quarter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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

Fons Ponsioen said:
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

Gwen said:
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
Click to expand...
Click to expand...
 
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

Gwen said:
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

Fons Ponsioen said:
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

Gwen said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top