database - Store two Queries result in third variable

Question

What's wrong with this code:

Visual Basic 6.0 With access 2007

Private Sub Command1_Click()
Dim Sell_tbl, Stock_Bottle, res As String


Sell_tbl = "SELECT Sum((Quantity)*12) FROM Sell_Detail Where Cateogry='Large'"
Stock_Bottle = "Select Sum(No_Of_Bottle) FROM Add_Bottle Where Cateogry='Large'"

res = ((Sell_tbl) - (Stock_Bottle))

Adodc1.RecordSource = Sell_tbl
Adodc1.Refresh
Adodc1.Caption = Adodc1.RecordSource
End Sub

Type Mismatch Error

I try to convert its result in other data type but it doesn't work. Can anyone help me?

Answer 1

Neither of these is a recordset, each is a string:

Sell_tbl = "SELECT Sum((Quantity)*12) FROM Sell_Detail Where Cateogry='Large'"
Stock_Bottle = "Select Sum(No_Of_Bottle) FROM Add_Bottle Where Cateogry='Large'"

You need something on the lines of:

Dim Sell_tbl As DAO.Recordset
Dim Stock_Bottle As DAO.Recordset

Set Sell_tbl = CurrentDB.Openrecordset _
    ("SELECT Sum((Quantity)*12) As Qty FROM Sell_Detail Where Cateogry='Large'")
Set Stock_Bottle = CurrentDB.Openrecordset _
    ("Select Sum(No_Of_Bottle) As Btl FROM Add_Bottle Where Cateogry='Large'")

res = Sell_tbl!Qty - Stock_Bottle!Btl

The above is a rough outline, it could do with tidying up.