display grandtotal from subtotal help

[monchichi2]

hello,please help.how to display the grangtotal from subtotal.

this is the current code:

Code: Select all


Set conDataConnection = New Connection
      conDataConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & _
                "\Overtime.mdb;Persist Security Info=False"
   
      Set rs = New ADODB.Recordset
    strSQL = "select Department,WorkDescript, SUM(OtHours) As OtHours from Overtime where OvertimeDate BETWEEN #" & start & "# and #" & enddate & "# Group by Department ,WorkDescript "
         
   
rs.Open strSQL, conDataConnection, adOpenStatic, adLockOptimistic

While Not rs.EOF
        If IsNull(rs("OtHours").Value) Then
            total = total + 0
        Else
            total = total + rs("OtHours").Value
        End If
        rs.MoveNext
    Wend
   
'set the database source in rs
   rptalldept.Sections("Section5").Controls.Item("Label15").Caption = Format(total, "#,##0.00")
   rptalldept.Sections("Section4").Controls.Item("Label19").Caption = start
   rptalldept.Sections("Section4").Controls.Item("Label21").Caption = enddate
   Set rptalldept.DataSource = rs
    'then set the field
    rptalldept.Sections("section1").Controls.Item("Text1").DataField = "WorkDescript"
    rptalldept.Sections("section1").Controls.Item("Text13").DataField = "OtHours"
    rptalldept.Sections("section1").Controls.Item("Text4").DataField = "Department"
    rptalldept.Sections("section1").Controls.Item("Text4").DataField = GrandTotal

 

thanks for help![/img]

[djohan]

Sorry, this answer might be a little out of topic (OOT) But, I'd suggest you to use Chrystal Report to generate reports.