hi I want to make a query that first find the sum of two columns of different table then subtract it
    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
3 Years
Discussion Span
Last Post by Sajjad_1

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

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.Caption = Adodc2.RecordSource

sum2 = Adodc3.RecordSource = sell_large
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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.