Compute All Possible Fractional Costs Of Items

[jrub]

Hi Everyone,

Im a long time lurking reading and have always loved the forum.

I have been looking for awhile for some help which will save me the rest of my hair on my head- I simply cannot get this going properly as I am a NOOB to MS Access and have played with looping record set but seem to cause so many errors in the VBA code I don't even know where to begin to fix it.

I need to generate fractional costs for multiple items based upon their life expectancy. Once their life expectancy is reached, it's fractional value restarts and continues, until a predefined set year (a field in a form). The restarting assumes items are automatically renewed.

In the attached example picture, the end year is set to 2000. Fractions are based on life expectancy, for example: 1/5, 2/5, 3/5, 4/5, 5/5 - this repeats because the item is renewed. For example, two items are listed below.

I still am clueless on how to perform the looping operation on each record until a specified year. The basic idea is the following:

My logic is as follow:

IF the (CurrentYear)-(OriginalYearBuilt) MOD (Life Expectancy)=0

True: 1 (representing renewal of the item and the multiplier is set to 1)

False:
[(CurrentYear)-(OriginalYearBuilt) MOD (Life Expectancy) ] / Life Expectancy

This represents the relative multiplier fraction.


I am unable to perform this on each record, iterativly from the original year built to the selected end date defined in a form's field?

Please, can anyone help me out there?

 

[MajP]

Untested but something like

Public sub loopInputs
  dim rs as dao.recordset
  dim endYear as integer
  set rs = me.recordset
  endYear = me.endYear
  do while not rs.eof
    setFractionCost(rs!ID, rs!originalYearBuilt, endYear, rs!itemDescription, rs!lifeExpectancy, rs!cost)
  loop
end sub

Public sub setFractionCost(ID as integer, OriginalYearBuilt as integer, endYear as integer, ItemDescription as string, lifeExpectancy as integer, cost as single)
  dim currentYear as integer
  dim i as integer
  dim strSql as string
  dim YearlyFraction as single
  dim FractionCost as single
  
  for I = 0 to (endYear - StartYear)
   currentYear = startYear + i
   IF the (CurrentYear - OriginalYearBuilt) MOD  LifeExpectancy = 0 then
      YearlyFraction = 1
   else
      YearlyFraction = ((CurrentYear - OriginalYearBuilt) MOD LifeExpectancy) / LifeExpectancy 
   end if
   FractionCost = YearlyFraction * cost
   strSql = "Insert into tblResults (ID, ItemDescription,[Year],YearlyFraction, FractionalCost) "
   strSql = strSql & "values (" & ID & ", '" & ItemDescription & "', " & CurrentYear & ", " 
   strSql = strSql & " YearlyFraction & ", " & FractionCost & ") "
   currentDb.execute strSql
  next i
end sub