Retrieve data from last row of specific Columns

[DMT Dave]

thanks for the prompt minty, this is something i should be able to do with one eye closed but a little brain freeze............



i will come back to it but as always sometimes a nudge is required so that is appreciated

[Gicu]

Dave,
Try this please, you need to store the previous record's value:

Dim rs As dao.Recordset    
Dim curRate As Currency, curDiff As Currency, curFuelPrice As Currency, curAvg As Currency, curMax As Currency
Dim i As Integer
Dim curPrevious as  Currency    
    
    
    
        Set rs = CurrentDb.OpenRecordset("Select * From tblFuelPrices Order By MonthStart")
            
            With rs
            
            curPrevious=!FuelPrice 'first record
            Do While Not rs.EOF            
                curDiff = !FuelPrice-curPrevious                    
                rs.Edit
                !Difference = curDiff
                .Update
                curPrevious=!FuelPrice
                rs.MoveNext            
            Loop
            
            End With
Set rs = Nothing

[DMT Dave]

Thank You Vlad, will read your method and test

[DMT Dave]

Vlad you absolute legend

That done the trick, my final part now is there is a field called PercentDiff, now calculate the percentage within that loop, i have a few various percentage formulas saved so i am now going to test some of my formulas to complete the additional data for this project

Thanks again

[DMT Dave]

Hi Vlad, all sorted, i think sometimes i just brain freeze on calculations and when to put variables inside and/or outside of a loop

This is all working with an addition percentage and differences, project complete

    Dim rs As dao.Recordset    Dim curRate As Currency, curDiff As Currency, curFuelPrice As Currency, curAvg As Currency, curMax As Currency, curPrevious As Currency
    Dim i As Integer
    Dim dblPercent As Double
    Dim curOrgPrice As Currency, curNewPrice As Currency
    
        Set rs = CurrentDb.OpenRecordset("Select * From tblFuelPrices Order By MonthStart")
            
            With rs
            curOrgPrice = "1234" ' Example of current price
            
            curPrevious = !FuelPrice 'first record
            Do While Not rs.EOF
                
                
                curDiff = !FuelPrice - curPrevious
                dblPercent = curDiff / !FuelPrice
                curNewPrice = curDiff / 4 '25% of difference
                
                rs.Edit
                !Difference = curDiff
                !PercentageDiff = dblPercent
                !orgCharge = curOrgPrice
                !ChargeDiff = curNewPrice
                
            If curNewPrice < curPrevious Then
                !NewCharge = curOrgPrice + curNewPrice
                Else
                !NewCharge = curOrgPrice - curNewPrice
            End If
                
                .Update
                dblPercent = !PercentageDiff
                curPrevious = !FuelPrice
                rs.MoveNext
            Loop
            
            End With
Set rs = Nothing

[Gicu]

Dave, glad to see you got it working and posted the working code!
Cheers,