Field Calculation Help

[brzfan2]

I have a table with one field in called Set Fiscal Yr. This holds the current fiscal yr code. I have a field in my receipt file called fiscal year. I want this field to auto-enter the fiscal year listed in the set fiscal year table. I tried to use a calculated field by having the receipt fiscal year field equal the set fiscal year field but that does not work. Any suggestions?

[comment]

You need either a relationship between the two tables, or the preset code must be in a global field. Why not simply calculate the fiscal year from the current date?

[brzfan2]

I tried to do that initially but nothing I tried worked. What calculation would I used?

[comment]

It depends on when your fiscal year starts, and what do you want to call it. For example:

Year ( date ) - ( Month ( date ) < 4 )

suits a fiscal year starting on April 1. It will return 2006 for any date between April 1 2006 and March 31 2007, inclusive.

[brzfan2]

Where "date" is do you put in the acutal date, 4/1/06 or do you get the current date? If you put in the actual date do you have to change this each time the fiscal year changes?

Also, I have to total the receipts for the fiscal year. Based on the fiscal year returned how would I build a calculation to do this?

[comment]

Where "date" is, you can put any expression that returns a date, for example Get(CurrentDate), or the name of a date field. Normally, a financial record would have a date field, usually set to auto-enter the creation date. The fiscal year calculation would be referencing that field, automatically returning the correct fiscal year for ANY date entered into the referenced field. It's a "set-and-forget" thing: the only time you would have to change anything would be if the start of your fiscal year were to change from April 1.

To sum by fiscal year you need a summary field (Total of Amount). Then either find the records of the desired fiscal year, or add a sub-summary part (when sorted by FiscalYear) to your report layout.

[brzfan2]

Thank you for your help. I have been struggling with the problem for a while.