0

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?

Simon Adcock
  • 3,554
  • 3
  • 25
  • 41
Sajjad
  • 11
  • 8
  • the minus suppose to be in the SQL language in your database, http://www.gokhanatil.com/2010/10/minus-and-intersect-in-mysql.html – jcho360 Feb 19 '14 at 20:57
  • Thanks a lot for your kindness I am new on this site try to avoid Mistake. – Sajjad Feb 19 '14 at 20:57
  • Thanks got it but can you highlighted why its show's type mismatch error because when I run both query separately it work's fine..... – Sajjad Feb 19 '14 at 21:03
  • This, [Subtract two Queries Result](http://stackoverflow.com/q/21875072/588306), [Use SQL Statement in access 2007](http://stackoverflow.com/q/21892646/588306), and [Using Adodc control to subtract Queries Result](http://stackoverflow.com/q/21908078/588306) are all the same question. – Deanna Feb 20 '14 at 13:22
  • @Deanna ok then give me a one answer satisfy my all Question – Sajjad Feb 20 '14 at 13:26
  • @Sajjad [This one](http://stackoverflow.com/a/21892727/588306) – Deanna Feb 20 '14 at 13:29
  • @Deanna you bound me with DAO control I told that I am not interested to use DAO control help with ADODC control.... – Sajjad Feb 20 '14 at 13:32
  • The same basic answer is correct, it's a pretty trivial change from DAO to ADO, expecially with the simplistic code given. – Deanna Feb 20 '14 at 13:35
  • @Deanna can you give me a link for guidance to learn DAO To ADO – Sajjad Feb 20 '14 at 13:38
  • Change "DAO" to "ADODB", and as you're already using ADO, you should have an open database object. You may need to change `OpenRecordSet()` to `Execute()` – Deanna Feb 20 '14 at 13:40
  • @Deanna I try but can't get the required result I use DAO but it didn't work please help – Sajjad Feb 20 '14 at 19:22
  • You'll have to expand on "can't get the required result" to get a better answer. The given answers give a numerically correct result. If it's not what you want, edit the question to be clearer, and maybe provide sample data. – Deanna Feb 21 '14 at 10:53

3 Answers3

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • what is meant by currentdb? the option is not shown in my program help please. – Sajjad Feb 20 '14 at 11:19
  • What program are you using? CurrentDb is available in MS Access modules. – Fionnuala Feb 20 '14 at 11:28
  • I am using visual basic 6.0 as front end and access as back end – Sajjad Feb 20 '14 at 11:30
  • one More question you connect db using DAO what about ADODB or adodc I didn't know too much about DAO so my first priority is adodc and ADODB Help please – Sajjad Feb 20 '14 at 11:33
  • DAO is nearly always faster with MS Access http://support.microsoft.com/kb/148361 – Fionnuala Feb 20 '14 at 11:38
  • tell me how to connect with ADODC control I'm not able to fetch table data when I Press a Button – Sajjad Feb 20 '14 at 11:45
  • I am not getting, it makes me confuse tell me the above code using adodc control – Sajjad Feb 20 '14 at 11:49
  • DAO is older Technology ..VB6 recommends using the ADO control instead of the older DAO-RDO.Tell me my problem with ADODC control Else Thanks all of you for Your Patient – Sajjad Feb 20 '14 at 19:59
  • DAO is not older technology, this is a constant misunderstanding. DAO is under continued development and a new version was released for Access 2010. VB6 is old technology. I am sorry, but I am not prepared to write old code for you. – Fionnuala Feb 20 '14 at 20:03
  • Also http://bytes.com/topic/access/answers/926400-discussion-pros-cons-dao-v-adodb – Fionnuala Feb 20 '14 at 20:19
0

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.

Wayne G. Dunn
  • 4,282
  • 1
  • 12
  • 24
0

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

Sajjad
  • 11
  • 8