Automatic generation of tax year

[Redhillgeneral]

I have a transaction table which will cover multiple years. I need to allocate each entry to the correct tax year; each tax year runs from 6th April to 5th April the following callender year.

The objective is that the tax year field will contain a formula that will calculate the correct tax year from the transation date (which is entered manually along with the remainder of the transaction data).

In Excel this could easily be achieved by means of a lookup table however this is not a concept used by Access



The final objective is to create reports containing only data related to a specific tax year.

[pbaldy]

this is not a concept used by Access

Not sure what you mean by this. A lookup table would be a common solution to the problem. You can also create a formula or function that does it.

[Redhillgeneral]

I have reviewed my Access Manual but it is very wooly on creating a function within a table which will refer to a date field within that table and then look that date up in another table which will hold the tax year. The design of the tax year table was Field 1 = start date of tax year (formated as dd/mm/yy)- Field 2 = Tax year (formated as yyyy). The alternative is for a function within the transaction table to calculate the tax year without the need to have a separate lookup table.

I have a transaction table which will cover multiple years. I need to allocate each entry to the correct tax year; each tax year runs from 6th April to 5th April the following callender year.

The objective is that the tax year field will contain a formula that will calculate the correct tax year from the transation date (which is entered manually along with the remainder of the transaction data).

In Excel this could easily be achieved by means of a lookup table however this is not a concept used by Access

The final objective is to create reports containing only data related to a specific tax year.

[pbaldy]

The function wouldn't be in the table, it would be in code, available for use anywhere in the application. Here's a little on functions:

http://www.baldyweb.com/Function.htm

and a very simple function I wrote within an application for a university. For any given date, it returns the start of the school year (theirs began 7/1):

Public Function SchoolYearStart(TestDate As Date) As Date
  If TestDate < DateSerial(Year(TestDate), 7, 1) Then
    SchoolYearStart = DateSerial(Year(TestDate) - 1, 7, 1)
  Else
    SchoolYearStart = DateSerial(Year(TestDate), 7, 1)
  End If
End Function

If you used the table, I'd personally include the end date, and you could look up the fiscal year any number of ways, including a join in a query.

[rpeare]

I have a similar situation (fiscal year instead of tax year) and I do this in queries or on forms/reports with a formula like this:

IIf(CInt(DatePart("m",[transactiondate]))=4,(IIf(CInt(DatePart("d",[transactiondate]))>=6,DatePart("yyyy",[transactiondate])+1,DatePart("yyyy",[transactiondate]))),IIf(CInt(DatePart("m",[transactiondate]))>=5,DatePart("yyyy",[transactiondate])+1,DatePart("yyyy",[transactiondate])))

Then you don't need to do any sort of lookup or any data conversion you just add this calculation as a field in your query and process only the tax year you want.

[Redhillgeneral]

Thanks for replies to date and sorry I have not replied earlier.

I am a novice at Access being more used to Excel. In Excel the formula I would use is =IF(AND(MONTH(Date)>=4,DAY(Date)>5),YEAR(Date)+1,Y EAR(Date)) where the field "date" is the one on which I want to base the expression on.

In my access database I want to use a form to input into a table. The form will have a field for "input date" and I want to add another field in the form which will calculate the tax year based upon the data input into the input date field. If I use the above excel formula it works however I have a problem in converting Excel formulae into Access expressions.

Any advice most welcome

[rpeare]

You don't need to store the fiscal year, it's a calculated value, all you have to store is the input date, any time you want to run fiscal year information you perform the fiscal year calculation in a query and set criteria on that calculated field.

In a query it would be a version of what I already gave you but you could also re-write it this way to be more in line with your excel formula:

FY: IIF(cint(datepart("m",(Date)))>=4 AND cint(datepart("d",(Date)))>5,datepart("yyyy",(Date ))+1,dateparty ("yyyy",(Date)))

Just a word of warning I didn't check brackets so they may be off but you shouldn't be using reserved words (Date, Month, Year, etc) in your object/field names it will cause you a lot of problems as you progress with your programming.