possible conflict in coding

  • Thread starter Thread starter alrae1
  • Start date Start date
A

alrae1

I am having trouble with a query which involves multiple parts. It is tied up
with asset management. In a nutshell there are three components CRC(Current
Replacement Cost), ACC_Dep (Accumulated depreciation),WDCC (Written down
current cost). Other commponents include difference between construction date
and current date, design life of asset.
When the date difference is equal to the design life the answer is 0 the e.g
CRC= $64960, WDCC= $4256 (it should read the same as CRC/Design life (in this
case 15)) and the ACC_dep is $64960 and it should read CRC-WDCC. As a result
the totalsof all the road assets is out and this only occurs when the date
difference is equal to the design life.
This occurs in three differnet areas Pavement (as above) Formation and
surface coding is the same in all three instances axcept Pav,Frm and Surf are
used. Provided coding for pavement

ACC_DepPav: IIf(([qry Areas]![Pavement Value]*((100/[qry Areas]!
[Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date()))>
[qry Areas]![Pavement Area]*[qry Areas]![Pavement_Cost],(([qry Areas]!
[Pavement Area]*[qry Areas]![Pavement_Cost])-([qry Areas]![Pavement_Salvage]*
[qry Areas]![Pavement Area])),([qry Areas]![Pavement Value]*((100/[qry Areas]!
[Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date())))

WDCCPav: IIf([qry Areas]![Pavement Value]-([qry Areas]![Pavement Value]*(
(100/[qry Areas]![Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]!
[YEAR],Date()))<=0,([qry Areas]![Pavement_Salvage]*[qry Areas]![Pavement Area]
),[qry Areas]![Pavement Value]-([qry Areas]![Pavement Value]*((100/[qry Areas]
![Pavement_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date())))

CRCpav: ([qry Areas]![Pavement Area]*[qry Areas]!Pavement_Cost)
 
I don't want to begin to try figure out the logic of this complex expression.
I have one recommendation: create a small user defined function that accepts
several fields as arguments and returns the correct value. There is no way
that I would create or maintain a complex business rule like this in a query
expression. Move it to a user-defined function in a module of business rules.
 
Back
Top