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

Dani AI

Generated

As is discovering, you cannot subtract two SQL text strings (Adodc.RecordSource is the SQL text). You need numeric results. Also note a VB6 pitfall: Dim sum1, sum2 As String makes only sum2 a String; sum1 is a Variant. Use numeric types (Integer/Long/Double) and handle possible NULLs from SUM (treat them as 0).

Two practical ways to fix this:

Use one SQL that returns the difference (then bind that single query to your control):

SELECT
  COALESCE((SELECT SUM(No_Of_Bottle) FROM add_cotton WHERE Category='Large'),0)
  - COALESCE((SELECT SUM(Quantity) FROM Sell_Detail WHERE Category='Large'),0)
  AS Remaining;

Or execute each SUM and subtract in VB, converting NULL to 0 (example uses ADODB.Execute and numeric variables):

Dim val1 As Double, val2 As Double
Dim rs As ADODB.Recordset

Set rs = conn.Execute("SELECT SUM(No_Of_Bottle) AS Total FROM add_cotton WHERE Category='Large'")
val1 = 0: If Not rs.EOF And Not IsNull(rs("Total")) Then val1 = CDbl(rs("Total"))
rs.Close

Set rs = conn.Execute("SELECT SUM(Quantity) AS Total FROM Sell_Detail WHERE Category='Large'")
val2 = 0: If Not rs.EOF And Not IsNull(rs("Total")) Then val2 = CDbl(rs("Total"))
rs.Close

Label1.Caption = CStr(val1 - val2)

If you keep using ADODC controls: set each control’s RecordSource to a query, call .Refresh, then read the numeric value from Adodc.Recordset.Fields(0).Value (not from RecordSource). For showing rows from two tables in one grid, create a single recordset (a view or a UNION ALL query) — a grid accepts one datasource, not two separate sources. Finally, check the column name spelling (Category) so your WHERE actually matches rows.

Recommended Answers

All 2 Replies

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.