grouping by fiscal year in a report

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

Guest

I am trying to produce a report that calculates total contributions by fiscal
year. I have a [gift date] field and a [gift amount field]. I know I have to
create a group header but I can't figure out how to write the expression to
create a grouping by fiscal years.
 
Dflynn01473 said:
I am trying to produce a report that calculates total contributions by fiscal
year. I have a [gift date] field and a [gift amount field]. I know I have to
create a group header but I can't figure out how to write the expression to
create a grouping by fiscal years.
Click to expand...


Maybe you should tell us what your fiscal year is??

Whatever it is you can group on an expression like:

=Year(DateAdd("m",3,[Gift Date]))

the 3 assumes that fiscal 2006 started on 1 Oct 2005
 
Thanks. I'll give it a try. The fiscal year is July 1 to June 30. I have a
gift date, with gifts going back several years. I want to sort these gifts by
the fiscal year in which they were recieved. I could use a fiscal year field
in the table but I'm trying to simplify data imput.
--
Dan


Marshall Barton said:
Dflynn01473 said:
I am trying to produce a report that calculates total contributions by fiscal
year. I have a [gift date] field and a [gift amount field]. I know I have to
create a group header but I can't figure out how to write the expression to
create a grouping by fiscal years.
Click to expand...


Maybe you should tell us what your fiscal year is??

Whatever it is you can group on an expression like:

=Year(DateAdd("m",3,[Gift Date]))

the 3 assumes that fiscal 2006 started on 1 Oct 2005
Click to expand...
 
Dflynn01473 said:
Thanks. I'll give it a try. The fiscal year is July 1 to June 30. I have a
gift date, with gifts going back several years. I want to sort these gifts by
the fiscal year in which they were recieved. I could use a fiscal year field
in the table but I'm trying to simplify data imput.
Click to expand...


Then change my 3 to a 6.

Let us know if it does what you want.
 
I couldn't seem to get this to work in Grouping and Sorting. So I tried it in
the query that the table was based on, creating a field: FY:
Year(DateAdd("m",+7,[Gift date])). I also tried out a field: FiscalYear:
IIf(Month([Gift date])>=7,Year([Gift Date])+1,Year([Gift Date])), which I got
and modified from a response to another users call for help on a fiscal year
problem. Both worked perfectly, except for two records with dates in late
June of 03. Your method put these in the 04 fiscal year. I can't imagine why.
Thanks again for your help.
--
Dan


Marshall Barton said:
Dflynn01473 said:
I am trying to produce a report that calculates total contributions by fiscal
year. I have a [gift date] field and a [gift amount field]. I know I have to
create a group header but I can't figure out how to write the expression to
create a grouping by fiscal years.
Click to expand...


Maybe you should tell us what your fiscal year is??

Whatever it is you can group on an expression like:

=Year(DateAdd("m",3,[Gift Date]))

the 3 assumes that fiscal 2006 started on 1 Oct 2005
Click to expand...
 
Dflynn01473 said:
I couldn't seem to get this to work in Grouping and Sorting. So I tried it in
the query that the table was based on, creating a field: FY:
Year(DateAdd("m",+7,[Gift date])). I also tried out a field: FiscalYear:
IIf(Month([Gift date])>=7,Year([Gift Date])+1,Year([Gift Date])), which I got
and modified from a response to another users call for help on a fiscal year
problem. Both worked perfectly, except for two records with dates in late
June of 03. Your method put these in the 04 fiscal year. I can't imagine why.
Click to expand...


That 7 should be a 6.

Both expressions should give the same result. I prefer :
Year(DateAdd("m", 6, [Gift date]))
because it is shorter and more intuitive to me.

Using it in the query is fine if you have a need to use a
criteria on it.

Don't forget that in the report's Sorting and Grouping, you
need to preceed it with an = sign
 
Yes, your approach is more elegant. I didn't quite unserstand the syntax
until your last psot about the six. It all makes sense now. Thanks.
--
Dan


Marshall Barton said:
Dflynn01473 said:
I couldn't seem to get this to work in Grouping and Sorting. So I tried it in
the query that the table was based on, creating a field: FY:
Year(DateAdd("m",+7,[Gift date])). I also tried out a field: FiscalYear:
IIf(Month([Gift date])>=7,Year([Gift Date])+1,Year([Gift Date])), which I got
and modified from a response to another users call for help on a fiscal year
problem. Both worked perfectly, except for two records with dates in late
June of 03. Your method put these in the 04 fiscal year. I can't imagine why.
Click to expand...


That 7 should be a 6.

Both expressions should give the same result. I prefer :
Year(DateAdd("m", 6, [Gift date]))
because it is shorter and more intuitive to me.

Using it in the query is fine if you have a need to use a
criteria on it.

Don't forget that in the report's Sorting and Grouping, you
need to preceed it with an = sign
Click to expand...
 
Back
Top