Sum data in MSFlexgrid.

[zynder]

Hi everyone!!!

I need help in summing these data by date.



How do i do this using one MSFlexgrid? After summing it, the output should only have 3 rows.

The expected output should be like this.

261 | 5986.67 | $652.9091 | 700.44 | $84.0528 | 2/1/2011
51 | 1959.09 | $201.2143 | 233.55 | $28.0260 | 2/2/2011
220 | 4379.10 | $475.3718 | 199.66 | $23.9592 | 2/3/2011

Any help is greatly appreciated.

[RhinoBull]

You may loop threough date column and sum each column into variable; when date changes write it to grid, clear it and go to the next record...

However, if you need sumarized data in the grid and data comes from database then why not use "Select Sum(some_field) ... Group By date_field" sql in the first place?

[zynder]

Hi thanks for the input. I already wrote some codes but i got a alternate blank rows. What is the code for clearing the row?

I cannot group by date field as my SQL is kinda complicated. Or what if i use a dummy MSflexgrid and put the values in there? What do you think?

[RhinoBull]

2 grids? Sounds unreasonable.
What's your sql look like? Maybe we can tune it for you if you post it...

[zynder]

Here is my SQL.

mySQL = "Select ServiceDate,Sum(QuantityStat) as QuantityStat,Sum(QuantityNonStat) as QuantityNotStat,Sum(AmountStat) as AmountStat, " & _
            "Sum(AmountNonStat) as AmountNotStat,Sum(DocumentCount) as DocumentCount,CustomerID From View_DocumentBillingReport " & _
            "Where ServiceDate between '" & DTPicker1.Value & "' and '" & DTPicker2.Value & "' And CustomerID in ("135, 136, 137") Group by CustomerID,ServiceDate"

[zynder]

here is my code snippet.

is my logic correct?

Private Sub Command1_Click()
Dim lngIndex As Long

For lngIndex = 1 To MSFlexGrid1.Rows - 1
    With MSFlexGrid1
        If .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex + 1, 6) Then
           .TextMatrix(lngIndex, 1) = CLng(.TextMatrix(lngIndex, 1)) + CLng(.TextMatrix(lngIndex + 1, 1))
           .TextMatrix(lngIndex, 2) = CDbl(.TextMatrix(lngIndex, 2)) + CDbl(.TextMatrix(lngIndex + 1, 2))
           .TextMatrix(lngIndex, 3) = CDbl(.TextMatrix(lngIndex, 3)) + CDbl(.TextMatrix(lngIndex + 1, 3))
           .TextMatrix(lngIndex, 4) = CDbl(.TextMatrix(lngIndex, 4)) + CDbl(.TextMatrix(lngIndex + 1, 4))
           .TextMatrix(lngIndex, 5) = CDbl(.TextMatrix(lngIndex, 5)) + CDbl(.TextMatrix(lngIndex + 1, 5))
           .TextMatrix(lngIndex, 6) = .TextMatrix(lngIndex, 6)
        End If
    End With
Next
End Sub