Get previous calculated field. 2

[Eprice]

Hello,
I think this must be impossible. I am trying to redo a dbase app in AccessXP. The table has a field PercentChange and I need to get RateBeforeChange and RateAfterChange from that field. dBase first set both RateAfterChange and RateBeforeChange to 100.00 and PercentChange to 0.00.
The report looks like this:
RateBeforeChange PercentChange RateAfterChange
100.00 0.00 100.00
100.00 20.60 120.60
120.60 0.00 120.60
120.60 14.30 137.85
137.85 0.00 137.85
137.85 16.50 160.60
160.60
RateBeforeChange needs to be what RateAfterChange was.
The calculation for RateAfterChange is
(((RateBeforeChange*(1+(PercentChange/100)))
Is this at all possible? I can't find any threads similar to this and have no idea where to start.
Lisa

 

[TheAceMan1]

how are ya Eprice . . .

This is really a [blue]generated list[/blue] that resides in a table.

If [blue]RateBeforeChange[/blue] & [blue]RateAfterChange[/blue] reside in the same table as [blue]PercentChange[/blue] this can be done with a recordset.

In a module in the modules window, copy/paste the following routine ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub GenList()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim RBC As String, RAC As String, PC As String, hldRAC As Currency
   
   Set db = CurrentDb
   RBC = "[purple][b]RateBeforeChange[/b][/purple]"
   RAC = "[purple][b]RateAfterChange[/b][/purple]"
   PC = "[purple][b]PercentChange[/b][/purple]"
   hldRAC = 100
   Set rst = db.OpenRecordset("tblChange", dbOpenDynaset)
   
   If Not rst.EOF Then
      Do
         rst.Edit
         rst(RBC) = hldRAC
         rst(RAC) = rst(RBC) * (1 + (rst(PC) / 100))
         hldRAC = rst(RAC)
         rst.Update
         rst.MoveNext
      Loop Until rst.EOF
   Else
      MsgBox "Table is Empty!"
   End If
   
   Set rst = Nothing
   Set db = Nothing

End Sub[/blue]

Call the routine from anywhere you like. Thats it!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Eprice]

That works perfect. Thanks AceMan1