Complicated Conditional Calculation

[Renee]

I am trying to design a database that houses information
about equipment. One of the most important things it
needs to do is run reports on depreciation. The only
variable in the depreciation formula is the original cost
of the equipment. However, depending on the exact date
the equipment was put in to service, what kind of
equipment it is, and how many years it has been in service
I would use a different formula.

For example:

If Type = Software and
If InServiceDate is Between 9/10/01 And 5/5/03 and
If YearsInService = 1
Then Depreciation = Cost*.5 + (Cost*.5)*.3333

But

If Type = Software and
If InServiceDate is Between 9/10/01 And 5/5/03 and
If YearsInService = 2
Then Depreciation = (Cost*.5)*.4445

If someone can just point me in the right direction, I
could probably take it from there. Right now I can't even
figure out how to get started with so many different
criteria.

Thanks for any help!
Renee

 

[Ken Snell]

Are you doing this in a query? Assuming that you are, create a calculated
field and use an expression similar to this for it:

Depreciation: IIf(Type = "Software" And InServiceDate Between #9/10/01# And
#5/5/03#, IIf(YearsInService = 1, Cost*.5 + (Cost*.5)*.3333,
IIf(YearsInService = 2, (Cost*.5)*.4445, 0), 0)

where the 0 amounts are the values that I have put in when the IIf statement
is false.

There are many ways to do this. If you have more criteria, then it is likely
that a different expression would be better to use, but without more info I
won't get into complications.

 

[Van T. Dinh]

Just to be sure, is there any relationship between
InService Date and YearsInService?

If it is straight out as you posted, you can use an
expression using IIF() function (IIf for Immediate If) but
I prefer to use a UDF so that you can extend it if you
need and the UDF is a lot clearer.

Something like:
****Untested****
Public Function fnDeprec( _
strType As String, _
dteInService As Date, _
intYearsInService as Integer,
curCost As Currency) As Currency

If (strType = "Software") AND _
(dteInService >= #09/10/2001#) AND _
(dteInService <= #05/05/2003#) Then
If intYearsInService = 1 Then
fnDeprec = curCost * 0.5 * 1.3333

ElseIf intYearsInService = 2 Then
fnDeprec = curCost * 0.5 * 0.4445
End If
End If
End Function
****

HTH
Van T. Dinh
MVP (Access)