K
kimbo
here's the problem......
i have a set of user entered fields;
[capital item name]
[start price]
[start date]
[tax year end] (that's the tax year end applicable to the item)
[write off amount year 1] (percentage of start price to write off in year 1)
[write off amount year 2 and on]
[check box] to indicate weather it's a proportional write off or not. (this
means that if in tax year one the item is only owned for say 10 days the
write off is only a ten day proportion of the yearly amount. and iff the item
is not proportional the full percentage is writen off in year 1).
i also have another table which contains a date field for the [tax year now
being worked with].
i need to be able to calculate the amount to write off for the tax year
being worked with.
ie.
iff the item is not yet purchased (the [tax year now being worked with] is
prior to the [start date],=0
iff the item is in tax year 1 (relative to the tax year being worked with)
And [check box]=0,([start price]/100)*[write off amount year 1]
iff the item is in tax year 1 (relative to the tax year being worked with)
And [check box]=-1,(([start price]/100)*[write off amount year 1])*"number if
days owned")
iff the item is now in it's 2nd tax year since purchase (relative to the tax
year being worked with)
it would be the result of the ("residual balance from year 1"/100)* [write
off amount year 2 and on]
iff the item is now in it's 3rd tax year since purchase (relative to the tax
year being worked with)
it would be the result of the ("residual balance from year 2"/100)* [write
off amount year 2 and on]
......... etc.
.....now i can make all this happen but only if i pre calculate all the
fields for say ten years and then work out what tax year i'm working with...
what i want is to be able to have only one resultant field which calculates a
value relative to [tax year now being worked with]....
can anyone please help me as i get lost with DatePart and DateDiff
calculations really quickly.
ps. as an asside;
could somone jot down how to make a query that either allows me too enter
the data in one field and have another calculated or enter the data in the
field and have the result calculated for the other end?
hmmm.... not to clear there...... so... field [a] is either entered or is
calculated as the result of say +[c]... but if [a] is entered then [c] is
the calculated field. ......make any sence?
thanks in advance.... kimbo
))
i have a set of user entered fields;
[capital item name]
[start price]
[start date]
[tax year end] (that's the tax year end applicable to the item)
[write off amount year 1] (percentage of start price to write off in year 1)
[write off amount year 2 and on]
[check box] to indicate weather it's a proportional write off or not. (this
means that if in tax year one the item is only owned for say 10 days the
write off is only a ten day proportion of the yearly amount. and iff the item
is not proportional the full percentage is writen off in year 1).
i also have another table which contains a date field for the [tax year now
being worked with].
i need to be able to calculate the amount to write off for the tax year
being worked with.
ie.
iff the item is not yet purchased (the [tax year now being worked with] is
prior to the [start date],=0
iff the item is in tax year 1 (relative to the tax year being worked with)
And [check box]=0,([start price]/100)*[write off amount year 1]
iff the item is in tax year 1 (relative to the tax year being worked with)
And [check box]=-1,(([start price]/100)*[write off amount year 1])*"number if
days owned")
iff the item is now in it's 2nd tax year since purchase (relative to the tax
year being worked with)
it would be the result of the ("residual balance from year 1"/100)* [write
off amount year 2 and on]
iff the item is now in it's 3rd tax year since purchase (relative to the tax
year being worked with)
it would be the result of the ("residual balance from year 2"/100)* [write
off amount year 2 and on]
......... etc.
.....now i can make all this happen but only if i pre calculate all the
fields for say ten years and then work out what tax year i'm working with...
what i want is to be able to have only one resultant field which calculates a
value relative to [tax year now being worked with]....
can anyone please help me as i get lost with DatePart and DateDiff
calculations really quickly.
ps. as an asside;
could somone jot down how to make a query that either allows me too enter
the data in one field and have another calculated or enter the data in the
field and have the result calculated for the other end?
hmmm.... not to clear there...... so... field [a] is either entered or is
calculated as the result of say +[c]... but if [a] is entered then [c] is
the calculated field. ......make any sence?
thanks in advance.... kimbo
))