CODEWITHSUNDEEP
sqlreportms-access-2013
Mark
Mark

Reputation: 704

Get previous calculated value in access report

I have a report that calculates several results from a table, Is there any way to get the previous value from a calculated camp inside a report?

Let's say I have a Text box A which is inside a "group" in a report, and I need to calculate another result that needs the previous value of A, example:

FieldA(1) = TableValue1  FieldB = null(cos FieldA  doesn't have "previous" values") / 54 * 152

FieldA(2) = TableValue2  FieldB = FieldA(1) / 54 * 152
FieldA(3) = TableValue3  FieldB = FieldA(2) / 54 * 152

Or is it better to reach this using sql?

Upvotes: 0

Views: 245

Answers (2)

Brad
Brad

Reputation: 12253

This only works in Print Preview mode (i.e. not normal report view mode) but you can use the Format event of the section in which the field you are trying to get the previous value for resides.

My table looks like this

enter image description here

The report looks like

enter image description here

And that On Format event code looks like this

Dim prev As String 'A form level variable (not in a sub/function)


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    txtPrevious = prev
    prev = Me("field1").Value
End Sub

It makes a report that looks like this

enter image description here

Again, it has to be run from Print Preview. Normal Report view does not fire the Format event

enter image description here

Upvotes: 1

Alexander Bell
Alexander Bell

Reputation: 7918

The answer to your question would be: implement a time series Tables on any specific fields that require to trace the historic values. For example, assuming you have an 'Employees' data Table with a 'Salary' field. In this case, new value overrides the previously entered one. In order to resolve this issue and be able to trace the historic values, create a second Table Salary linked to the main Table "Employees" on the primary id fields, add 'Salary' field and a TimeStamp field, so you will be able to trace the Employee's salary history.

Your Report will be based on the Join between two Table, thus you would be able to display the current (latest) and previous values sorted on TimeStamp in descending order.

Hope this will help.

Upvotes: 0

Related Questions