DATE calculations..... ooo er missus!

[kimmole]

anyone got the right syntax to calculate....

the fields i have are...
[tax year start] (short date)
[tax year end] (short date)
[start date] (short date)
[purchase price] (currency)
[write off year 1] (number)
[write off year 2 and beyond] (number)
[proportional check] (check box)
[res bal fwd] (currency)(calculated)
[sold date] (short date)
[sold price] (currency)

i need to calculate field [x]....
iif(datepart("Y",[start date]) is between [tax year start] and [tax year end] calculate days to [tax year end]from start date,0000

then i need to calculate field [res bal fwd]... (to run as update qry at year end)
iif(is not null[sold date],0,
iif([proportional check]=-1 AND [x]<365,[purchase price]-(((([purchase price]/365)*[x])/100)*[write off year 1]),
iif([proportional check]=0 AND [x]<365,([purchase price])-(([purchase price]/100)*[write off year 1]),
[x]=0000,([res bal fwd])-(([res bal fwd]/100)*[write off year 2 and beyond])


then i need field [y]....
iif(is not null[sold date],[sold price]-[res bal fwrd],
iif([proportional check]=-1 AND [x]<365,(((([purchase price]/365)*[x])/100)*[write off year 1]),
iif([proportional check]=0 AND [x]<365,(([purchase price]/100)*[write off year 1]),
[x]=0000,([res bal fwd]/100)*[write off year 2 and beyond]

....... can ya see what it is yet kids???

 

[PaulBricker]

1. What are the dates for [tax year start] and [tax year end].
2. Are the other IIf blocks supposed to be one large nested IIf statement that you need help with?

Paul

 

[kimmole]

tax year end 05/april...tax year start 06 april
as to iif ....yes i spose they are nested i'm using the line end comma to mean else... i think

thanks for any help in advance