display grandtotal from subtotal help

[gracehskuo]

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

this is the current code:

     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.

[wes4dbt]

Not clear where the problem is, a little more description would be helpful.

I did notice this,

rptalldept.Sections("section1").Controls.Item("Text4").DataField = "Department"
rptalldept.Sections("section1").Controls.Item("Text4").DataField = GrandTotal

You are assigning two values to the same control.

Also you can use the "SUM()" control in the group footer and report footer for totals.

[gracehskuo]

wes4dbt:
hi,thanks ya reply.i want to get the grandtotal from the subtotal.

for exampls:
department category work subtotal of category work
Admin General work 10
Admin others 15
grandtotal:25 of department Admin from the subtotal of category work
department category work subtotal of category work
PC General work 40
PC keyin 15
grandtotal:55 of department Admin from the subtotal of category work

[wes4dbt]

Why wouldn't the "SUM()" function do this for you?

[gracehskuo]

wes4dbt,
hi,thanks you reply.how to SUM the grandtotal??please help.

this is the current code:

strSQL = "select Department,WorkDescript, SUM(OtHours) As OtHours from Overtime where OvertimeDate BETWEEN #" & start & "# and #" & enddate & "# Group by Department ,WorkDescript "

thanks for help.

[wes4dbt]

Isn't this a VB6 DataReport?
If so, there is "SUM" control. you would put it in the group footer section and set the Datafield property to Othours.

[gracehskuo]

yes i use vb6 datareport.ok i will try it.thanks ya