Accumulated Depreciation

[Klssri]

We are using Access to calculate depreciation which works fine. Our problem is that we want to have it stop accumulating depreciation once the value becomes 0. We have tried numerous things but nothing seems to work. Here is what we are doing.

Current Depreciation: (([CapitalValue]-[SalvageValue])/[Dep Life])

Accumulated Depreciation: (DateDiff("yyyy",[Assets]![DateAcquired],Now())+Int(Format(Now(),"mmdd")<Format([Assets]![DateAcquired],"mmdd")))*([Current Depreciation])

It seems like we need something in this calculated field that says that if accumulated depreciation is greater than or equal to capital value to stop. Accumulated depreciation should never be greater than the capital value. We tried to add the following as a criteria but it made no difference. We came up with the same results.

IIf([Accumulated Depreciation]>[CapitalValue],"[CapitalValue]","[Accumulated Depreciation]")

Not sure if this all makes sense or not. But I hope it does to someone. Any help would be appreciated.

 

[PHV]

And without double quotes ?
IIf([Accumulated Depreciation]>[CapitalValue],[CapitalValue],[Accumulated Depreciation])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Klssri]

Thanks for the post, but that did not work either. Any other ideas out there.

 

[PHV]

Perhaps the hard way ?
Accumulated Depreciation: IIf((DateDiff("yyyy",[Assets]![DateAcquired],Now())+Int(Format(Now(),"mmdd")<Format([Assets]![DateAcquired],"mmdd")))*(([CapitalValue]-[SalvageValue])/[Dep Life])>[CapitalValue], [CapitalValue], (DateDiff("yyyy",[Assets]![DateAcquired],Now())+Int(Format(Now(),"mmdd")<Format([Assets]![DateAcquired],"mmdd")))*(([CapitalValue]-[SalvageValue])/[Dep Life]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Klssri]

Tried that and got an error message that we had an undefined function in Iff statement. Our question is can we use an Iff statement to begin a calculated field? Any other ideas.