grouping by fiscal year in a report

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

 

[Marshall Barton]

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.

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

 

[Guest]

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

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.

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

 

[Marshall Barton]

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.

Then change my 3 to a 6.

Let us know if it does what you want.

 

[Guest]

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

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.

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

 

[Marshall Barton]

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.

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

 

[Guest]

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

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.

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