Print a total when it come up a certain character

[rainford]

form1 proj 4 1
form1 proj 3 O
form1 proj 1 I
form1 proj 1 C
_
9
form1 proj 2 I
form2 proj 1 O
_
3
form3 proj 1 I
form3 proj2 2 O
_
3

Does anyone know how to get this to work. I want a total to be printed where the fourth column has a value of "C" or one of the first two column changes

 

[scriverb]

There doesn't seem to be a logical sorting order to these records other than the order they were entered so I am going to assume that to be the case. Try this.

Create another field in your table called SortOrder as a Number Long field type.

Before you run your report you must run the following code to create the sorting and grouping data necessary for your report. This new field will be the field to control the grouping footer to be printed.

Dim vSortValue As Long
Dim vForm As String
Dim vProj As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblTestData", dbOpenDynaset)
vSortValue = 1
rs.MoveFirst
vForm = rs("FormName&quot
vProj = rs("ProjName&quot
Do
If (rs(&quot;FormName&quot = vForm) And (rs(&quot;ProjName&quot = vProj) And (rs(&quot;Col4&quot <> &quot;C&quot Then
rs.Edit
rs(&quot;SortOrder&quot = vSortValue
rs.Update
Else
vForm = rs(&quot;FormName&quot
vProj = rs(&quot;ProjName&quot
rs.Edit
rs(&quot;SortOrder&quot = vSortValue
rs.Update
vSortValue = vSortValue + 1
End If
rs.MoveNext
Loop Until rs.EOF

In the Report select the Sort And Grouping button and sort the data on the NEW field SortOrder only and select YES for the Group Footer property. This will give you the footer area available to put your control to total the 3rd column.

I hope this helps you out.

Bob Scriver