Member Avatar for Sajjad_1 
hi I want to make a query that first find the sum of two columns of different table then subtract it
    Example
    it is a visual basic program

    Dim large_tbl As String
    Dim sell_large As String

    large_tbl = "SELECT Sum(No_Of_Bottle) FROM add_cotton where Cateogry='Large'"
    sell_large = "SELECT Sum(Quantity) FROM Sell_Detail where Cateogry='Large'"

    Adodc2.RecordSource = large_tbl - sell_large

    Please help
    it shows an error type miss match
  • 2 Contributors
  • 2 Replies
  • 1K Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by Sajjad_1
Member Avatar for abelingaw

Sorry but you can't just actually subtract (or any arithmetic operation) 2 SQL statements. (or maybe for UNION or JOINS)

What you need to do is modify your 2 SQL then subtract the value of 2 specifice columns on those tables.

Dim sum1, sum2 as String

sum1 = Adodc1.Recordset.Fields("Fieldname") 'replace fieldname with your column name
sum2 = Adodc2.Recordset.Fields("Fieldname2") 'replace fieldname2 with your column name

'I used Adodc1 & Adodc2 since you can't assign 
'2 Recordsource for a single Adodc control

Label1.Caption = sum1 - sum2 'if you are to display the result on a label control

Not actually the correct code, but you'll get it.

Edited by abelingaw
Member Avatar for Sajjad_1 
i can't get you firstly, and why i use recordset instead of record source i use your formula but can't get success my code is 

Dim sum1, sum2 As String

sum1 = Adodc2.RecordSource = large_tbl
Adodc2.Refresh
Adodc2.Caption = Adodc2.RecordSource

sum2 = Adodc3.RecordSource = sell_large
Adodc3.Refresh
Adodc3.Caption = Adodc3.RecordSource

MsgBox sum1 - sum2

one more question is that

how to conact data gird with two sources is it possible...
Thanks a lot for your guidence
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.