Dear Friends,
I have a report which columns given below.
Sno Project Name Project No Bill Raise Amount
1 abc 0213 YES 123.33
2 xyz 0232 NO 23.33
3 asd 0365 YES 125.65
4 rty 0325 254.52
5 hjkg 0254 NO 236.22
and i am using dataenvironment->command1
My requirement is Sum Amount (YES) ?
Sum Amount (NO) ?
Sum Amount ( ) ?
I can get only one at one datareport
Please tell how can i get all of above individual amount (YES and NO and blank) in one data report in a single data report
try this
'declaration section
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Command1_Click()
'now calculate the sum you require step by step or you can create multiple recordsets and append all at once and update your table
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select sum(amt)as amt from table1 where bill='YES'"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
db.BeginTrans
db.Execute ("update table1 set tmpyes=" & rs!amt)
db.CommitTrans
End If
rs.Close
Set rs = Nothing
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select sum(amt)as amt from table1 where bill='NO'"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
db.BeginTrans
db.Execute ("update table1 set tmpno=" & rs!amt)
db.CommitTrans
End If
rs.Close
Set rs = Nothing
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open ("select sum(amt)as amt from table1 where bill is null"), db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
db.BeginTrans
db.Execute ("update table1 set tmpblk=" & rs!amt)
db.CommitTrans
End If
'after this call report
DataEnvironment1.rsCommand1_Grouping.Properties.Refresh
DataEnvironment1.rsCommand1_Grouping.Open
DataReport1.Show vbModal
DataEnvironment1.rsCommand1_Grouping.Close
End Sub
Private Sub Form_Load()
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\db1.mdb;"
Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2
End Sub
I AM ALSO ATTACHING THE SOLVED SAMPLE.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.