Show percentage movement on previous figure

[Stuart Green]

I have a basic report that shows sales figures for a number of years. What I would like to do is show the percentage movement up or down for a year when compared to the previous one. For example

Year1 £5000
Year2 £7000 40.00%
Year3 £7000 0.0%
Year4 £5800 -17.1%
Year5 £7000 20.68%

Any suggestions as to where to start would be gratefully received

 

[Stuart Green]

It's OK, I've had a Eureka moment and solved it using a global variable to hold the previous year's figures. I created a global variable as follows


Public BetVar As Currency




For every customer (group) I reset the variable to zero

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

BetVar = 0

End Sub

Then in the details, the following

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If BetVar = 0 Then
Me.PercentMove = 0
Else
Me.PercentMove = (YearTotal - BetVar) / BetVar

End If

BetVar = Me.YearTotal


End Sub

where PercentMove is a field on the report