Financial Year Format

[mcalex]

Hi all

I have a form that allows input of historical finance data. I want to give the user a field where (s)he can input a financial year (eg 2009/10, 2007/08).

Do I do this as a date field, or a string field?

I've had a search, and I think this was a similar question (http://www.access-programmers.co.uk/forums/showthread.php?t=154246), but the answer involves a financial year being calculated from a regular date.

I want to allow a financial year as input - and hopefully run queries against the field (eg find the total of <FinanceItem1> for the five years of 2000/01 -> 2004/05).

Thank you for any assistance

mcalex

 

[pbaldy]

Personally I'd likely use a numeric field that represented the fiscal year (like 2010 for 2009/10). Using a string of "2009/10" would make Between criteria problematic, I would think. It would also eliminate the variety of inputs you could get in a text field (2009/2010, 09/10, etc).

 

[mcalex]

yeah, but all the reports come in with "... for the Financial Year 20xx/yy"

I was going to use a combo for the input field as I can guarantee that no matter how many times it is explained, I won't get 100% consistency on the translation of a financial year to the correct calendar year by the input ppl.

OK, so a lookup-type table that associates a Year to a FinancialYear; a combo box bound to the Year field, but which displays the FinancialYear field, and I do all my total queries against the Year field?

I think that makes sense.

thank you

 

[John Big Booty]

If you run with pbaldy's suggestion You can still get your report to be titled as "... for the Financial Year 20xx/yy", by using something like;

"... for the Financial Year " & [YearField]-1 & "/" & [YearField]-2000

Of course if you have dates pre 2000 that you may with to run reports on you would need to insert a logical test to deal with that circumstance and return the appropriate values.

 

[namliam]

Numbers can be misleading as ... you make the user deside what financial year something belongs to...

IMHO you would be better served to enter the date of transaction in a proper date field, like the link does then use a function to calculate the financial year as also in the link.

Now the advantage of proper dates are many fold, among which.... the ability to drill down into Half year /quarters/months, this requirement probably dont exist yet, but when it does come (and IMHO it is quite likely to come) you will be very happy you went this route...

 

[gemma-the-husky]

i actually think you DO need a year/period indicator

the trouble with just using a date is that stuff can be processed late, and the true item date may not then reflect the accounting period in which it has been processed

so one (traditional) way is to have a current accounting period, which you close at the period end - which imposes discipline on your system.

then the reporting becomes a question of selecting the year/period range you want to review

 

[namliam]

In which case you have a "action date" and a "processing date" possibly even a "accounting date" or some sort....

Regardless, keeping "metadata" in the Year/Period way (2009/10) vs "detail data" (14/09/2010) has the distinct 'problem' of losing details. I cannot even start counting how many times I have encountered that people were keeping Metadata about one thing or another, only to find out *later* that it would have been very helpfull to keep the detail(s).