Store two Queries result in third variable

问题:

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?

回答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.

回答2:

The reason for the error is because of statement:

s = ((Sell_tbl) - (Stock_Bottle))

If you look above that line, you are setting two string variables to SQL -- which is text not numeric. You need to open recordsets with those sql strings, then get the results, then perform the math.

回答3:

It is what I want....

Private Sub Command2_Click()
Dim con As New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
     & App.Path & "\add_entry.mdb;Persist Security Info=False"

Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim result_hold As Integer

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'"

rs.Open large_tbl, con, adOpenDynamic, adLockOptimistic
rs1.Open sell_large, con, adOpenDynamic, adLockOptimistic

result_hold = CInt(rs.Fields(0).Value) - CInt(rs1.Fields(0).Value)
Text1.Text = CStr(result_hold)
End Sub

'if u need to retreive whole colum use loop or etc.. but one thing is remember to you two sources 'never attach with single grid...