Calculate total of a listview column

[CupraSA]

Hi,

Please help.

I've have a listview that contails 10 columns. The records listed in the control change based on the month selected.

I now need to display a sub total at the end of the timespent column and dont have a clue how to do this...

I've searched all over the net and was only able to find this site which proves it is possible:

http://www.thevbprogrammer.com/Extras_HTML/ListViewExtras.htm

Please can someone assist with sample code

Private Sub LoadTimeSheetListView_Month()
    Dim strSQL      As String
    Dim objCurrLI   As ListItem

    strSQL = "SELECT *" _
           & "  FROM TimeSheet WHERE username = '" & MainUser.Text & "' AND MonthNum = '" & txtMonthChange.Text & "'" _
           & " ORDER BY Record"
    
    mobjCmd.CommandText = strSQL
    Set mobjRst = mobjCmd.Execute
    
    ListViewGrid.ListItems.Clear
    
    With mobjRst
        Do Until .EOF
            Set objCurrLI = ListViewGrid.ListItems.Add(, , !Record & "", , "Custs")
            objCurrLI.SubItems(mlngREC_DATE_IDX) = !DATERECIEVED & ""
            objCurrLI.SubItems(mlngREC_WEEK_IDX) = !Weekday & ""
            objCurrLI.SubItems(mlngREC_USER_IDX) = !UserName & ""
            objCurrLI.SubItems(mlngREC_PROJ_IDX) = !ProjectName & ""
            objCurrLI.SubItems(mlngREC_AREA_IDX) = !FuncArea & ""
            objCurrLI.SubItems(mlngREC_BUSI_IDX) = !Business & ""
            objCurrLI.SubItems(mlngREC_HOUR_IDX) = !timespent & ""
            objCurrLI.SubItems(mlngREC_DESK_IDX) = !Description & ""
            objCurrLI.SubItems(mlngREC_WNUM_IDX) = !WeekNum & ""
            objCurrLI.SubItems(mlngREC_MNUM_IDX) = !MonthNum & ""
            objCurrLI.SubItems(mlngRCRD_ID_IDX) = CStr(!Record)
            .MoveNext
        Loop
    End With
    
    With ListViewGrid
        If .ListItems.Count > 0 Then
            Set .SelectedItem = .ListItems(1)
            ListViewGrid_ItemClick .SelectedItem
        End If
    End With
    
    Set objCurrLI = Nothing
    Set mobjRst = Nothing

End Sub

 

[AndyGroom]

Within the Do... ...Loop why not add a line saying TotalTimeSpent = TotalTimeSpent + !timespent and then after the loop just add an empty record where only the TotalTimeSpent is displayed?

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[AndyGroom]

Sorry, senior moment, I was confusing !timespent with timespent! and I don't think what I wrote will work.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[Hypetia]

>I don't think what I wrote will work.

I think it will!

 

[Andrzejek]

You can do what Andy suggested, or you can:

With ListViewGrid
    If .ListItems.Count > 0 Then[blue]
        strSQL = "SELECT SUM(timespent) as SumTime " _
           & " FROM TimeSheet WHERE username = '" & MainUser.Text _
        & "' AND MonthNum = '" & txtMonthChange.Text & "'"[/blue]
        [green]'Display SumTime here in ListViewGrid[/green]
        Set .SelectedItem = .ListItems(1)
        ListViewGrid_ItemClick .SelectedItem
    End If
End With

Have fun.

---- Andy

 

[CupraSA]

Thanks for the prompt responces guys. Ok I went with this option.

Just one more thing: How do you get it to add SumTime in the correct column. Currently it adds it to the first column. It needs to be displayed beneath the timespent column. Also is it possible to make the text bold or change the color of the row?

    With ListViewGrid
        If .ListItems.Count > 0 Then
            strSQL = "SELECT SUM(timespent) as SumTime " _
            & " FROM TimeSheet WHERE username = '" & MainUser.Text _
            & "' AND MonthNum = '" & txtMonthChange.Text & "'"
            Set .SelectedItem = .ListItems(1)
            ListViewGrid_ItemClick .SelectedItem
            
            mobjCmd.CommandText = strSQL
            Set mobjRst = mobjCmd.Execute
    
            'MsgBox "Total hours worked = " & mobjRst!SumTime
            Set objCurrLI = ListViewGrid.ListItems.Add(, , mobjRst!SumTime, , "Custs")
            End If
    End With

 

[AndyGroom]

To get it in the right column you need to need the right number of (empty) columns before you add the total, or you could place a textbox on screen in the relevant place and put the total in that, which would allow you to set the font and bold attributes. I don't think you can change the font attributes for a specific list item in a listview.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments