Calculations within Access Reports! Help!

[asmrani1]

Hey everyone,

I have collected some information from a SQL database and dumped them into an ACCESS table.

Now I want to report on this table and make some calculations.

This calculations is basically to figure out the change between the rows
the table looks like this:

RowNUM TimeRecords Contacts Logins Durations
1 1298 12 63 41
2 1300 15 72 42
3 1600 20 52 43

Now I want the change between rownum 1 and 2, and 2 and 3 and so forth?? How would I do that?

Any clues? Ideas? Thanks!!

[olivthill]

The differences between two rows can be computed in the OnFormat event of the Detail section.
Here is how this can be done:

1. Layout of the report

In the detail section of the report, you place fields associated with your columns.
Then, you duplicate these fields, by a copy/paste next to them.
In the property page of the original fields, you set visible=false.
In the property page of the duplicated fields,
you enter a name that is better than the default_name,
you remove whatever is in the controlSource property.

2. VB code

Open the window of Visual Basic, and write the following lines

Dim col1_previous_value, col2_previous_value

Private Sub Report_Open(Cancel As Integer)
col1_previous_value = 0
col2_previous_value = 0
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
col1_delta.Value = col1.Value - col1_previous_value
col2_delta.Value = col2.Value - col2_previous_value
col1_previous_value = col1.Value
col2_previous_value = col2.Value
End Sub

Of course, you have to replace "col1", "col2", "col1_detla", "col2_delta" by the actual name of your columns.
col1 and col2 are the hidden fields containing the data from a row, and col1_delta and col2_delta are the duplicated fields with no data associated with them.

[asmrani1]

thank you very much...

one more question: what are the previous_value i mean in which fields??

[olivthill]

The data, that I named colx_previous_value, are not fields. They are variables declared at the beginning of the VB script, and available to every function in the script.

Dim col1_previous_value, col2_previous_value

[asmrani1]

Thank YOU very much...you da man!!