dcsimg
Results 1 to 5 of 5

Thread: How to sum selected row data in data report ?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2009
    Posts
    108

    Question How to sum selected row data in data report ?

    Hi,
    if i want to sum all data in one columns is no problem, how about if i only want to sum selected row??
    refer to below image file

    my code:
    Code:
    Private Sub Command1_Click()
        
       Set rs = New ADODB.Recordset
        With rs
            .Open "SELECT * FROM [test.txt]", _
                  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
                & App.Path _
                & "';Extended Properties='Text'", _
                  adOpenStatic, adLockReadOnly, adCmdText
            Set DataReport1.DataSource = rs
        End With
        
    DataReport1.Show vbModal
    
    SumQty
    DataReport1.Sections("Section5").Controls("Label6").Caption = Text1.Text
    RSCheck
                
    End Sub
    
    Public Sub SumQty()
    
    RSCheck
    rs.Open "Select sum(Number) From [test.txt]", cn, adOpenStatic, adLockReadOnly, adCmdText
    If Not IsNull(rs.Fields(0)) Then
        Text1.Text = rs.Fields(0)
    End If
    
    End Sub
    
    Public Sub RSCheck()
    If rs.State = 1 Then rs.Close
    End Sub
    your help is much appreciated
    Name:  sample.jpg
Views: 218
Size:  88.1 KB

  2. #2

  3. #3
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Smile Re: How to sum selected row data in data report ?

    Try the following way . hope it might help .
    Code:
    Public Sub SumQty()
    
    
    rs.Open "Select barcode,count(number), sum(Number) From [test.txt]" group by barcode , cn, adOpenStatic, adLockReadOnly, adCmdText
    If Not IsNull(rs.Fields(0)) Then
        Text1.Text = rs.Fields(0)
    End If
    rs.close
    set rs=nothing 
    End Sub

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Aug 2009
    Posts
    108

    Re: How to sum selected row data in data report ?

    thank you for your code, just now i try, but unfortunately not a correct figure (quantity is 001)? correct figure quantity is 106584.

  5. #5
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: How to sum selected row data in data report ?

    oh sorry I also did not notice .try the following . hope it might help .
    Code:
    Public Sub SumQty()
    dim rs as new adodb.recordset
    rs.Open "Select Count(*),barcode sum(Number) From [test.txt]" group by barcode , cn, adOpenStatic, adLockReadOnly, adCmdText
    If Not IsNull(rs.Fields(0)) Then
        Text1.Text = rs.Fields(0)
    End If
    rs.close
    set rs=nothing 
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width